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
*/