The use of function Merge (update、insert、delete)

1、The use of function merge(update、insert、delete)

Example:

#1.Initialize the data

create table #test(id int ,value int); create table #test2(id int ,value int);

insert into #test values(0,0)

insert into #test values(1,1)

insert into #test values(2,2)

insert into #test values(3,3)

insert into #test2 values(1,11)

insert into #test2 values(2,22)

insert into #test2 values(5,55)

select * from #test

select * from #test2

#2.T-SQL

merge into #test p using (select id,value from  #test2) np on (p.id = np.id)        

--Update the record when source id matched target id

when matched then      

update set p.name = np.value

--Insert the record when source id not matched target id 

when not matched then        

insert values (np.id,np.value)

--Delete the records when source id not matched target id and source value >= 2

when not matched by source and value>= 2 then delete;

#3.The result

select * from #test

id value
0 0
1 11
2 22
5 55