Creating SQL table using dynamic variable name

Question

I want to create backup SQL tables using variable names.

something along the lines of

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

but i'm getting

Incorrect syntax near '@SQLTable'.

It's just part of a small script for maintence so i don't have 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 can't use bind variables for table names, column names, etc. IN this case you must generate dynamic SQL and use exec.