你的位置:首页 > 数据库

[数据库]SqlServer中Merge的使用


引用自:http://www.cnblogs.com/CareySon/archive/2012/03/07/2383690.html

感谢CareySon的分享,我把使用到的SQL脚本贴上来。

--创建源表 CREATE TABLE SourceTable(ID INT,[DESC] VARCHAR(50));

--创建目标表 CREATE TABLE TargetTable(ID INT,[DESC] VARCHAR(50));

--为源表插入数据

INSERT INTO SourceTable VALUES(1,'描述1');

INSERT INTO SourceTable VALUES(2,'描述2');

INSERT INTO SourceTable VALUES(3,'描述3');

INSERT INTO SourceTable VALUES(4,'描述4');

INSERT INTO TargetTable VALUES(1,'在源表中存在,将会被更新');

INSERT INTO TargetTable VALUES(2,'在源表中存在,将会被更新');

INSERT INTO TargetTable VALUES(5,'在源表中不存在,将会被删除');

INSERT INTO TargetTable VALUES(6,'在源表中不存在,将会被删除');

MERGE INTO TargetTable AS T

USING SourceTable AS S ON T.ID=S.ID

WHEN MATCHED THEN UPDATE SET T.[DESC]=S.[DESC]

WHEN NOT MATCHED THEN INSERT VALUES(S.ID,S.[DESC])

WHEN NOT MATCHED BY SOURCE THEN DELETE

OUTPUT  

  $ACTION AS [ACTION],INSERTED.ID AS 插入的id,Inserted.[DESC] AS 插入的DESC,  

  DELETED.ID AS 删除的id,DELETED.[DESC] AS 删除的DESC;