How do I Create a Table with a variable name

[mholbert]

I am working on a stored procedure to export data from a sql 2005 database. I would like to have the stored procedure create a new table in the database, and then populate it. I am planning on creating a job to have the stored procedure run once a month, and would therefore like the table named based on the current month.

In short, is there a way to create a table whose name is determined by a variable?

P.S.
I have considered using the snapshot capability in sql 2005, but I haven't been able to find a way to base the snapshot on a sql query, instead of the entire database.

Any help is appreciated.

MH

 

[LNBruno]

Sounds like you'll need to create a dynamic SQL statement.

< M!ke >
Acupuncture Development: a jab well done.

 

[ca8msm]

You'll have to use dynamic sql. e.g.

DECLARE @TABLENAME varchar(50)
DECLARE @SQLString nvarchar(500)

SET @TABLENAME = 'Test'
SET @SQLString = 'CREATE TABLE ' + @TABLENAME + ' (id int, description varchar(50))'

EXEC sp_executesql @SQLString

____________________________________________________________
Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]

Need help finding an answer? Try the Search Facility or read FAQ222-2244.

 

[mholbert]

awesome, thanks for the help. MH