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