May 22, 2019 at 5:17 pm
It gives me an error message as follows: I am trying to backup a table using the SELECT * INTO ....
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '@BkTblName'.
DECLARE @BkTblName varchar(100);
DECLARE @SQLString nvarchar(500);
DECLARE @ParmDefinition nvarchar(500);
SELECT @BkTblName = 'HRAHistory_20190521_XX';
SELECT @ParmDefinition = N' @BkTblName VARCHAR(100) ';
SELECT @SQLString = N'Select * INTO @BkTblName FROM GHP_HRA.HRAHistory_20190521 ';
EXECUTE sp_executesql @SQLString, @ParmDefinition, @BkTblName=@BkTblName ;
May 22, 2019 at 5:59 pm
I don't think it works like this. Parameter is what you put on the WHERE clause. If you want change table name, concatenate:
SELECT @SQLString = N'Select * INTO ' + @BkTblName + ' FROM GHP_HRA.HRAHistory_20190521 ';
--Vadim R.
May 22, 2019 at 6:05 pm
Cool, It worked! Thanks
May 22, 2019 at 9:08 pm
I don't think it works like this. Parameter is what you put on the WHERE clause. If you want change table name, concatenate:
SELECT @SQLString = N'Select * INTO ' + @BkTblName + ' FROM GHP_HRA.HRAHistory_20190521 ';
I don't recommend that on it's own, it'll suffer from injection. Quote your objects when using Dynamic SQL:
SET @SQLString = N'SELECT * INTO ' + QUOTENAME(@BkTblName) + N' FROM GHP_HRA.HRAHistory_20190521;';
Ideally, as well, you should be declaring your schema name in the statement as well (which as it isn't specified, I assume is dbo
).
You could even go further and check the validity of the table, which I explain how to do here: Dos and Don'ts of Dynamic SQL.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply