sql merge
--源表
CREATE TABLE SourceTable(id INT,remark NVARCHAR(50))
--目标表
SELECT * INTO TargetTable FROM SourceTable
SELECT *
FROM SourceTable
SELECT *
FROM TargetTable
--TRUNCATE TABLE SourceTable
--TRUNCATE TABLE TargetTable
--插入数据
INSERT INTO SourceTable
SELECT 1,'描述1' union all
SELECT 2,'描述2' union all
SELECT 3,'描述3' union all
SELECT 4,'描述4'
INSERT INTO TargetTable
SELECT 1,'update' union all
SELECT 2,'update' union all
SELECT 5,'delete' union all
SELECT 6,'delete'
--实例
MERGE INTO TargetTable AS t
USING SourceTable AS s
ON t.id=s.id
WHEN MATCHED
THEN UPDATE SET t.remark=s.remark
WHEN NOT MATCHED
THEN INSERT VALUES(s.id,s.remark)
WHEN NOT MATCHED BY SOURCE
THEN DELETE
OUTPUT $ACTION AS [ACTION],
INSERTED.id AS insertID,
INSERTED.remark AS insertRemark,
DELETED.id AS deleteID,
DELETED.remark AS deleteRemark;