Creating an SQL Table Using a Dynamic Variable Name

Question

I want to create backup SQL tables with variable names.

something along the lines

DECLARE @SQLTable Varchar(20) SET @SQLTable = 'SomeTableName' + ' ' + '20100526' SELECT * INTO quotename(@SQLTable) FROM SomeTableName 

but i get

Invalid syntax near '@SQLTable'.

This is just part of a small script to maintain, so I don't need to worry about injections.

Answer 1

 DECLARE @MyTableName nvarchar(20); DECLARE @DynamicSQL nvarchar(1000); SET @MyTableName = "FooTable"; SET @DynamicSQL = N'SELECT * INTO ' + @MyTableName + ' FROM BarTable'; EXEC(@DynamicSQL); 

Answer 2

Unfortunately, you cannot use bind variables for table names, column names, etc. In this case, you must create dynamic SQL and use exec .

Answer 3

 DECLARE @Script NVARCHAR(MAX); SET @Script = N'SELECT * INTO SomeTableName_' + N'20100526' + N' FROM SomeTableName'; EXEC sp_executesql @Script 

I left the date separate, as I assume that you want to calculate it for each run.

Answer 4

You should learn synonyms:

- Create a synonym for the Product table in AdventureWorks2008R2. CREATE SYNONYM MyProduct FOR AdventureWorks2008R2.Production.Product; GO

- Request a product table using a synonym. USE tempdb; GO SELECT ProductID, Name FROM MyProduct WHERE ProductID <5; GO

http://msdn.microsoft.com/en-us/library/ms177544.aspx

Answer 5

 DECLARE @MyTableName nvarchar(20); DECLARE @DynamicSQL nvarchar(1000); SET @MyTableName = "FooTable"; SET @DynamicSQL = N'SELECT * INTO ' + @MyTableName + ' FROM BarTable'; exec @DynamicSQL; 

this query is correct, but just use a single quote in ("FooTable") = "FooTable"