October 16, 2007 at 10:57 pm
I am trying to work some logs which are put into MSDE tables with names such as sw_sysdblog_2007_10_15 that have identical structures and are built at the rate of one (and only one) table per day.
Ideally I would like to be able to union them together in a view based on selecting a subset (last week's logs, etc). I've looked at partitioned views but these seem a bit high-end for the situation and most of the examples cover only hard-coded table names. I would rather not use a cursor for this, which is the other direction that it seems likely as a solution, because 1) I haven't worked with cursors and 2) I'll be taking this out to MS Access or Excel for further manipulation.
Any ideas?
Thanks,
Steve
October 17, 2007 at 5:26 am
I suggest you look into a stored procedure that accepts a start and an end date.
Your problem is that your table name is dynamic and for this reason only I suggest you look into dynamic SQL like
SELECT * FROM @tblname.
@tblname is the table name appended by a conversion of the date you are looking for.
Build a loop on the datediff of the two dates and append the SQL command with
UNION SELECT * from @tblname.
Finally executed by EXEC. This can then be executed in Query Analyzer (for copy & paste) or as a job dropping the result into a table for later analysis.
As this is a DBA routine the quick dirty way using such programming.
If you want to know more about why Dynamic SQL is a poor way of doing this, read the following article.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply