March 11, 2005 at 11:54 am
I have a request to take a snapshot of a table every Saturday. The table is updated daily in a DTS package using an Execute SQL step. So I'm thinking after this update I want to do something like:
If DatePart(dw,GetDate()) = 7
Begin
Select *
Into TableName + '_' + DatePart(m,GetDate()) + '_' + DatePart(d, GetDate()), + '_' + DatePart(yy,GetDate())
From TableName
End
The trouble is that T-SQL won't accept an expression that evaluates into a string as the table name. So I'm wondering if there is some way I can build the snapshot table name into a string, generate the snapshot into a table with some arbitrary name like 'TableSnapshot' and then change the name of that table to my dated table name?
Of course, if there is a better way to get to the same results of a snapshot with the date as the last part of the table name, then I'm willing to learn about it and change strategies. Anyone know how to get this done?
March 11, 2005 at 11:57 am
You'd need to use dynamic SQL to achieve this - build the SQL for the 'SELECT INTO ...' into a string and EXEC the resulting string.
Alternatively, build into a staticly named table and then execute a sp_rename to name it with the date, but again, you're going to have to use dynamic SQL to build the sp_rename parameter list.
March 11, 2005 at 12:49 pm
For a different take:
Why not have the SELECT INTO go against a VIEW. You could set-up a job that runs once a week to CREATE TABLE and ALTER VIEW (using dynamic SQL).....
Good Hunting!
AJ Ahrens
webmaster@kritter.net
March 11, 2005 at 1:21 pm
Dynamic SQL was exactly what I wasn't quite seeing. Thanks for the suggestion. I worked quite easily.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply