SQL Server的Merge —— 一步實現 insert,update,delete

USE tempdb
GO
IF OBJECT_ID('SourceTable') IS NOT NULL
DROP TABLE SourceTable
IF OBJECT_ID('TargetTable') IS NOT NULL
DROP TABLE TargetTable

--源
CREATE TABLE SourceTable(id INT,[desc] NVARCHAR(50))
--目標
CREATE TABLE TargetTable(id INT,[desc] NVARCHAR(50))

--源表資料
INSERT INTO SourceTable VALUES (1,'描述1'),(2,'描述2'),(3,'描述3'),(4,'描述4')

--目標表資料
INSERT INTO TargetTable VALUES (1,'在源表裡存在，將會被更新')
INSERT INTO TargetTable VALUES (2,'在源表裡存在，將會被更新')
INSERT INTO TargetTable VALUES (5,'在源表裡不存在，將會被刪除')
INSERT INTO TargetTable VALUES (6,'在源表裡不存在，將會被刪除')

SELECT * FROM SourceTable AS st
/*
id  desc
1   描述1
2   描述2
3   描述3
4   描述4
*/

SELECT * FROM TargetTable AS tt
/*
id  desc
1   在源表裡存在，將會被更新
2   在源表裡存在，將會被更新
5   在源表裡不存在，將會被刪除
6   在源表裡不存在，將會被刪除 
*/

--注意事項 
--1.最後的分號必須有
--2.源表可以是一個具體的表,也可以是一個子查詢語句
--3.2008或以上版本才有的功能

MERGE INTO TargetTable AS T     --可新增 TOP 限制操作行數: MERGE TOP(2)
USING SourceTable AS S
ON T.id=S.id
WHEN MATCHED            --當兩者的id能匹配，id=1,2的資料被更新
    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
OUTPUT $ACTION AS [Action]
    ,INSERTED.id AS [插入的id]
    ,INSERTED.[desc] AS [插入的DESC]
    ,DELETED.id AS [刪除的id]
    ,DELETED.[desc] AS [刪除的DESC]
;
/*
Action  插入的id   插入的DESC 刪除的id   刪除的DESC
INSERT  3             描述3          NULL         NULL
INSERT  4             描述4          NULL         NULL
UPDATE  1             描述1          1        在源表裡存在，將會被更新
UPDATE  2             描述2          2        在源表裡存在，將會被更新
DELETE  NULL          NULL         5        在源表裡不存在，將會被刪除
DELETE  NULL          NULL         6        在源表裡不存在，將會被刪除 
*/

SELECT * FROM SourceTable AS st
/*
id  desc
1   描述1
2   描述2
3   描述3
4   描述4 
*/
SELECT * FROM TargetTable AS tt
/*
id  desc
1   描述1
2   描述2
3   描述3
4   描述4
*/