1. MERGE用法:關(guān)聯(lián)兩表,有則改,無則加
SQL語句:
create table #AAA(id int,A int,AA int,AAA int,B int)create table #BBB(A int,B int)insert into #AAA select 1,1,1,1,null union select 2,2,2,2,null union select 3,3,3,3,null union select 4,4,4,4,nullinsert into #BBB select 1,10 union select 2,20 union select 3,30 union select 6,60merge into #AAA as t using (select * from #BBB where A<30 )as son s.A=t.Awhen matched then update set t.B=s.Bwhen not matched by target then insert values(0,s.A,0,0,s.B)when not matched by source then update set t.B=0output $action as [Action], Inserted.id as InsertId, Inserted.B as InsertB, Deleted.id as DeletedId, Deleted.B as DeletedB;