C.P.Hardcastle


Afternoon all,

Is it possible from within SQL Server Management Studio to create a table based upon an existing table using the current date as part of the table name

I.E; SELECT * FROM TABLENAME INTO TABLENAMEWITHDATE - if this query was setup as a SSMS Agent Job we could create a daily snapshot of data in this table.

I've tried many times but always get an incorrect syntax message when I try to excecute the query. I'm not sure what syntax I should use to create the tablename with current date included

Any help would be appreciated.

Thanks,

Chris




Re: Create Table with current date as part of the table name

Louis Davidson


Though I am wary of what you are trying to do (a permanent table with a column fro the load date is usually easier to work with,) you could use dynamic SQL:

declare @tableName varchar(8), @query nvarchar(1000)

set @tableName = convert(varchar(8), getdate(),112)

select @query = 'select name into ' + quotename(@tableName) + ' from sys.objects'

exec (@query)

select *
from sys.objects
where name = @tableName







Re: Create Table with current date as part of the table name

C.P.Hardcastle

Thanks, Louis, you've been a great help.

If you ever find yourself lost in Chepstow I'll definately be buying your drinks.

Chris