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.
DECLARE @MyTableName sysname;
DECLARE @DynamicSQL nvarchar(max);
SET @MyTableName = 'FooTable';
SET @DynamicSQL = N'SELECT * INTO ' + QUOTENAME(@MyTableName) + ' FROM BarTable';
EXEC sp_executesql @DynamicSQL;
Unfortunately, you can't use bind variables for table names, column names, etc. IN this case you must generate dynamic SQL and use exec.
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.
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
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'
