May 12, 2009 at 10:38 pm
Hi, I have a table in the database which is 'GSK_ReEnrolment' and I am trying to insert in my table 'GSK_ReEnrolment' but getting error message. Please advise. Thanks.
--Script
DECLARE @tbCueUp TABLE(empid VARCHAR(10), sin VARCHAR(9), code VARCHAR(8))
DECLARE @WE_ClientCode VARCHAR(10)
DECLARE @EventCode VARCHAR(10)
SELECT @WE_ClientCode = 'GSK'
SELECT @EventCode = 'ENRA'
INSERT INTO @tbCueUp
SELECT empid, sin ,@EventCode
FROM ctor_pdb06pf25.PF25GSK_Prod.dbo.member m WHERE m.status in ('Q') and m.surname<>'Tester'
DELETE FROM + @WE_ClientCode + '_ReEnrolment '
INSERT INTO + @WE_ClientCode + '_ReEnrolment '
SELECT EmpID, Sin FROM @tbCueUp
--Error which I am getting
Incorrect syntax near '+'.
May 12, 2009 at 11:01 pm
Yes, the problem is right here:
...
DELETE FROM + @WE_ClientCode + '_ReEnrolment '
INSERT INTO + @WE_ClientCode + '_ReEnrolment '
SELECT EmpID, Sin FROM @tbCueUp
...
You cannot use a variable or an exprssion for the table name of a static SQL statement. You will need to use dynamic SQL to do this.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 12, 2009 at 11:05 pm
HI,
How can i use the dynamic variable to solve the problem. Please advise for the script. Thanks.
May 12, 2009 at 11:20 pm
Like this:
...
Declare @sql as Varchar(MAX)
Select @sql = 'DELETE FROM '+@WE_ClientCode+'_ReEnrolment;'
+' INSERT INTO '+@WE_ClientCode+'_ReEnrolment ;'
+' SELECT EmpID, Sin FROM '+@tbCueUp+';'
EXEC(@sql);
...
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 13, 2009 at 11:42 am
Hi, Still getting following error message.
Must declare the variable '@tbCueUp'
May 13, 2009 at 11:52 am
Sorry, I missed that @tbCueUp was a table variable instead of a string variable. Try it like this:
...
Declare @sql as Varchar(MAX)
Select @sql = 'DELETE FROM '+@WE_ClientCode+'_ReEnrolment;'
+' INSERT INTO '+@WE_ClientCode+'_ReEnrolment ;'
EXEC(@sql);
SELECT EmpID, Sin FROM @tbCueUp;
...
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 13, 2009 at 9:50 pm
Hi, Still this script is not working and not able to insert records into 'GSK_Reenrolment' table. Please advise. Thanks.
May 13, 2009 at 9:57 pm
petersobeco (5/13/2009)
Hi, Still this script is not working and not able to insert records into 'GSK_Reenrolment' table. Please advise. Thanks.
Well I'm a pretty good guesser but I'm afraid that you'll have to give me some kind of clue here. What is the error that you are getting and what does you script currently look like?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply