Merge的使用

--Merge的使用(参考网址:http://www.cnblogs.com/CareySon/archive/2012/03/07/2383690.html)----创建原表Create Table SourceTable(id int,[DESC] varchar(50))----创建目标表Create Table TargetTable(id int,[DESC] varchar(50))----为原表插入数据Insert Into SourceTable(id,[DESC]) values (1,'描述1')Insert Into SourceTable(id,[DESC]) values (2,'描述2')Insert Into SourceTable(id,[DESC]) values (3,'描述3')Insert Into SourceTable(id,[DESC]) values (4,'描述4')----为目标表插入数据Insert Into TargetTable(id,[DESC]) values (1,'在原表里存在,将会被更新')Insert Into TargetTable(id,[DESC]) values (2,'在原表里存在,将会被更新')Insert Into TargetTable(id,[DESC]) values (5,'在原表里不存在,将会被删除')Insert Into TargetTable(id,[DESC]) values (6,'在原表里不存在,将会被删除');

select * from SourceTable;select * from TargetTable;----用法MERGE INTO TargetTable AS TUSING SourceTable AS SON T.ID=S.ID --后可加条件WHEN MATCHED --当on后面的T.ID=S.ID,数据将被更新THEN UPDATE SET T.[DESC]=S.[DESC]WHEN NOT MATCHED --当目标表中没有id,原表中有,则被插入THEN INSERT VALUES(S.ID,S.[DESC])WHEN NOT MATCHED BY SOURCE --当目标表中存在,原表中不存在,则删除THEN DELETE;