CODEWITHSUNDEEP
sqlsql-serverdatabaset-sqldynamic-sql
stevenjmyu
stevenjmyu

Reputation: 946

Creating SQL table using dynamic variable name

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.

Upvotes: 13

Views: 54716

Answers (5)

John Hartsock
John Hartsock

Reputation: 86862

DECLARE @MyTableName sysname;
DECLARE @DynamicSQL nvarchar(max);

SET @MyTableName = 'FooTable';


SET @DynamicSQL = N'SELECT * INTO ' + QUOTENAME(@MyTableName) + ' FROM BarTable';

EXEC sp_executesql @DynamicSQL;

Upvotes: 25

Chandu
Chandu

Reputation: 11

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 single quote at the ("FooTable")='FooTable'

Upvotes: 1

Tyson Nero
Tyson Nero

Reputation: 2078

You should look into using synonyms:

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

-- Query the Product table by using the synonym. USE tempdb; GO SELECT ProductID, Name FROM MyProduct WHERE ProductID < 5; GO

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

Upvotes: 4

Don
Don

Reputation: 9661

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

I've left the date separate as I assume you want to calculate it for every run.

Upvotes: 5

Donnie
Donnie

Reputation: 46903

Unfortunately, you can't use bind variables for table names, column names, etc. IN this case you must generate dynamic SQL and use exec.

Upvotes: 6

Related Questions