Category: SQL Server

30 Nov 2016 14:18

Joined:	04/08/2008
Level:	Silver
Points:	116

Points:	5

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

30 Nov 2016 16:51

Joined:	18/12/2008
Level:	Gold
Points:	2921

Points:	4

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

30 Nov 2016 18:29

Joined:	30/04/2012
Level:	Gold
Points:	7233

Points:	3

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

Error while adding multiple records using merge statement

About dotnetspider.com

Quick Links

General

Services