Query with changing table names

We have a web tracking program that came with our firewall 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 w 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?
I an trying to do this through an Access 2003 .ADP. I posted to that user
group and was refered here.
Thanks in advance for your suggestions.
SteveYou could set up a job to create a new view each day that would select from
the "ellusive" tables.
Tables names must be listed explicitly - wildcards are not applicable.
Basically, what you need is a view like in this example:
select <columns>
from <table_name>_2005-10-27
union all
select <columns>
from <table_name>_2005-10-28
union all
select <columns>
from <table_name>_2005-10-29
...
ML|||>> 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... <<
You have just re-discovered 1950's magnetic tape processsing! You
almost mimicked the IBM naming conventions which gave tape labels the
markers 'yyddd' that we used when we did not have RDBMS!
Steve, you reallllllllllly need to stop programming and catch up with
RDBMS technology. You have missed the basics of RDBMS.
You keep the same data in the same table, period. A table is a set of
ALL --repeat ALL-- of those data elements. This is founjdations, not
rocket science. In RDBMS, you have a column (NOT a field like in the
1950's) that gives a duration, then you create VIEWs or derived tables
or subqueries.|||> You keep the same data in the same table, period.
well we have been using partitioning and UNION ALL views for quite a
while, not because we like doing it, but because it really boosts
performance. The performance price of following the advice to "keep the
same data in the same table" would be so huge - anyone actually doing
so might be fired on the spot...
Makes sense?|||I agree with your observations, however the program was written by the
firewall manufacturer so I have no control over it. I was surprised to see
how they setup this up but they aren't going to make changes for little 'ol
me. Their reports are lacking to say the least, so that's why I'm stuck with
this problem. Since I can't fix their program, are there any suggestions on
how to create a query that can pull the data from these tables together so
it's useful?
Thanks
Steve
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1130449917.894438.155260@.z14g2000cwz.googlegroups.com...
> You have just re-discovered 1950's magnetic tape processsing! You
> almost mimicked the IBM naming conventions which gave tape labels the
> markers 'yyddd' that we used when we did not have RDBMS!
> Steve, you reallllllllllly need to stop programming and catch up with
> RDBMS technology. You have missed the basics of RDBMS.
> You keep the same data in the same table, period. A table is a set of
> ALL --repeat ALL-- of those data elements. This is founjdations, not
> rocket science. In RDBMS, you have a column (NOT a field like in the
> 1950's) that gives a duration, then you create VIEWs or derived tables
> or subqueries.
>|||I have hacked a script togethor that might do what you want. Let me
know what you think. I used a couple of previous posts on this group to
help. I am a bit of a newbie myself. I have tested the script as far as
the creation of the UNION SQL but not the overall procedure.
Celtic_Kiwi
/* Script begins */
CREATE PROCEDURE upLogViewCreate
AS
declare @.tablename varchar(32)
declare @.sql varchar(4000)
declare tnames_cursor cursor
for
select name
from sysobjects
where type = 'U'
and left(name,3) = '200'
open tnames_cursor
fetch next from tnames_cursor into @.tablename
set @.sql = ''
while (@.@.fetch_status <> -1)
begin
if (@.@.fetch_status <> -2)
begin
if @.sql = ''
begin
set @.sql = 'select * from ' + @.tablename
end
else
begin
set @.sql = @.sql + ' union select * from ' + @.tablename
end
end
fetch next from tnames_cursor into @.tablename
end
CLOSE tnames_cursor
DEALLOCATE tnames_cursor
if ( (exists
(select *
from dbo.sysobjects
where id = object_id(N'[dbo].[vwLogView]')
and OBJECTPROPERTY(id, N'IsView') = 1
)
and
(@.sql<>'')
)
drop view [dbo].[qryNotesCandiac]
EXEC('CREATE VIEW dbo.vwLogView
AS ' + @.sql )|||>> we have been using partitioning and UNION ALL views for quite a while, no
t because we like doing it, but because it really boosts performance <<
No, we did it until SQL engines did a good job of implemenation that we
did not have to. Have you seen what DB2 does to detect a DW (fact and
dimension tables) ?
That is why we do not ever confuse implementation with logical models.
A partitioned table is not like multiple table at all. You are still
thinking that logical = physical because you are priogramming as if
this was the 1960's.|||You can do this with dynamic sql it goes like this;
steps;
1. select all tables from sysobjects table one after another
2. put that in to variable and use union query in dynamic sql
3. use exec(that query string) or sp_executesql
4. you have all rows in one view/derived table
Regards
R.D
--Knowledge gets doubled when shared
"Steve Roberts" wrote:

> We have a web tracking program that came with our firewall that writes it'
s
> data to a MSDE database.
> Unfortunately it writes each day's data to a different table. It names the
m
> connection_events_2005_10_20 then connection_events_2005_10_21 etc... I ne
ed
> to create a report by the w 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?
> I an trying to do this through an Access 2003 .ADP. I posted to that user
> group and was refered here.
> Thanks in advance for your suggestions.
> Steve
>
>|||>> The performance price of following the advice to "keep the same data in
It depends on the specific schema/DBMS/physical implementation and the
performance price cannot be generalized. However the integrity issues
involved in doing so can be generalized. For some details, google for
"Principle of Orthogonal Design"
Anith|||Could you try creating views in advance of the firewall creating the tables?
then point all the views at the same table and have it log into a single
place?
IIRC, MSDE comes with sql agent and you could schedule a job to create the
views 7 days in advance, and destroy them once they're "old".
"Steve Roberts" <Stever@.Discussiongroups.com> wrote in message
news:OMwmRM12FHA.3296@.TK2MSFTNGP09.phx.gbl...
> I agree with your observations, however the program was written by the
> firewall manufacturer so I have no control over it. I was surprised to see
> how they setup this up but they aren't going to make changes for little
'ol
> me. Their reports are lacking to say the least, so that's why I'm stuck
with
> this problem. Since I can't fix their program, are there any suggestions
on
> how to create a query that can pull the data from these tables together so
> it's useful?
> Thanks
> Steve
>
> "--CELKO--" <jcelko212@.earthlink.net> wrote in message
> news:1130449917.894438.155260@.z14g2000cwz.googlegroups.com...
<<
>