Error during inserting records

  • 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 '+'.

  • 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]

  • HI,

    How can i use the dynamic variable to solve the problem. Please advise for the script. Thanks.

  • 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]

  • Hi, Still getting following error message.

    Must declare the variable '@tbCueUp'

  • 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]

  • Hi, Still this script is not working and not able to insert records into 'GSK_Reenrolment' table. Please advise. Thanks.

  • 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