Query with changing table names

  • Thread starter Thread starter Steve Roberts
  • Start date Start date
S

Steve Roberts

I have a web tracking program that writes it's data to a MSDE database.
Unfortunately it writes each day's data to a different table. It names them
connection_events_2005_10_20 then connection_events_2005_10_21 etc... I need
to create a report by the week from all of these tables. Is there away to
query all of the tables that start with "connection_events_ " at the same
time? Is there a different way to deal with this?

Thanks in advance for your suggestions.

Steve
 
Sorry to be rude but this a bad design: you should use a single table and
add a field for the date. This is how relational datas are designed to
work.

If you really want to, you can write a SP that will query for the names of
all of your tables by using either the system tables or the
INFORMATION_SCHEMA views (see m.p.sqlserver.programming or Google for more
details on this), append the names of these tables into query string with
multiples UNION and SELECT and execute this string as your resultset (or
append the data from all relevant tables into a temporary table and select
this last table as the result for the report); however, if you are able to
achieve this, then you should be able to redesign your tables in the first
place.

If you try to keep coding your schema this way - using a different table for
each set of dates - then the number of problems (and headaches) that you
will encounter will keep growing at an exponential rate.
 
Sylvain,

I totally agree with you! Unfortunately this database is created by our
firewall software so I have no control over it. I was shocked to see how
they set this up. I really don't understand what their reasoning was.

Thanks for the direction. I'll do some looking looking into the system
tables and INFORMATION_SCHEMA to see if I can figure it out from there. I
was just not sure where to start looking.

Steve
 
Back
Top