You must Sign In to post a response.
  • Category: SQL Server

    Error while adding multiple records using merge statement

    Hi,

    I want to add multiple rows in a table using merge statement

    following is my code

    If object_ID(N'tempdb.dbo.#admin') is not NULL
    Drop table #admin;
    create table #admin
    (
    uname varchar(10) primary key,
    age int,
    dept nvarchar(50)

    )



    declare @words varchar(max), @sql nvarchar(max)
    set @words = 'jain,30,Civil;baiju,28,Computer'

    set @sql = 'merge #admin AS target
    using (@words) AS source(uname, age,dept)
    on target.uname = source.uname
    when not matched by source then insert into #admin values ( source.[uname], source.[age], source.[dept];)'

    --print @sql
    exec(@sql);

    select * from #admin
    ............................................................................
    when I run the query Iam getting following error

    Msg 1087, Level 15, State 2, Line 2
    Must declare the table variable "@words".
    Msg 102, Level 15, State 1, Line 4
    Incorrect syntax near ';'.

    (0 row(s) affected)

    How to solve this

    Regards

    Baiju
  • #768420
    Hi,

    The problem is you mixing both Dynamic and Non Dynamic query in a single procedure.
    Kindly modify your query something like this,



    set @sql = '
    declare @words varchar(max), @sql nvarchar(max)
    set @words = 'jain,30,Civil;baiju,28,Computer'

    merge #admin AS target
    using (@words) AS source(uname, age,dept)
    on target.uname = source.uname
    when not matched by source then insert into #admin values ( source.[uname], source.[age], source.[dept];)'

    exec(@sql);

    select * from #admin




    Thanks,
    Mani

  • #768424
    You can use MERGE statement to adding multiple records using merge statement
     MERGE into <target table>
    USING
    <source table/view/result of subquery>
    ON
    <match condition>
    WHEN MATCHED THEN
    <update clause>
    <delete clause>
    WHEN NOT MATCHED THEN
    <insert clause>

    Useful reference : http://stackoverflow.com/questions/22925699/sql-server-multiple-insert-with-merge


Sign In to post your comments