Create a Variable Table name?? 2

[tEkHEd]

Hi I want to be able to keep historic data, and need to archive tables.

to do this I want the table to have some form of datestamp, I have decided that I want to use the Julian Date (Day of Year) as the suffix.

I have managed to create the dynamic table name, but cannot seem to use a variable for this table name in an SELECT -- INTO statement.. :¬?

Can anyone tell me why this syntax does not work??

DECLARE @DOY AS CHAR(3), @TBLPREF AS CHAR(14), @TBLNAME AS CHAR(19)
-- Retrieves the Day of the Year (Julian Date)
SET @DOY = (SELECT DATENAME(dy, GETDATE()))
SET @TBLPREF = 'dbo.tblArchive'
SET @TBLNAME = @TBLPREF+@DOY

print @tblname -- prints the correct table name of dbo.tblArchiveXXX where XXX is Julian Date

SELECT *
INTO @TBLNAME -- Incorrect syntax here...
from tblSource

Do I have to use a different query for this? Is there a Rename command that I could use instead?

Thanks in adv

 

[JamesLean]

You cannot use variables in place of object names. You must use dynamic SQL here:

DECLARE @sql varchar(200)
SET @sql = 'SELECT * INTO ' + @tblname + ' FROM tblsource'
EXEC(@sql)

--James

 

[tEkHEd]

Hehe.. so simple.. really should have thought about that.. thanks for waking me up James