13

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.

Luke Girvin
  • 12,913
  • 8
  • 60
  • 81
stevenjmyu
  • 908
  • 4
  • 16
  • 31

5 Answers5

23
DECLARE @MyTableName nvarchar(20);
DECLARE @DynamicSQL nvarchar(1000);

SET @MyTableName = "FooTable";


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

EXEC(@DynamicSQL);
John Hartsock
  • 82,242
  • 22
  • 125
  • 144
  • 6
    exec @DynamicSQL should be: exec(@DynamicSQL); -- without the parenthesis, throws "name not a valid identifier". – plditallo Apr 29 '14 at 19:15
  • 1
    I have the same result as @plditallo. Without the parenthesis I get error: "Could not find stored procedure..." EXEC without brackets attempts to call a procedure. https://stackoverflow.com/questions/8382753/calling-exec-generates-error-could-not-find-stored-procedure – Gerhard Liebenberg Feb 17 '18 at 16:19
6

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

Donnie
  • 43,578
  • 10
  • 63
  • 85
4
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.

Don
  • 9,161
  • 4
  • 25
  • 25
4

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

Community
  • 1
  • 1
Tyson Nero
  • 1,998
  • 5
  • 24
  • 34
1
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'

Danila Ganchar
  • 8,684
  • 12
  • 42
  • 65
Chandu
  • 11
  • 1