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;