使用MERGE语句同步表

先建好测试环境：

 

USE TEMPDB
GO
IF OBJECT_ID('T1') IS NOT NULL DROP TABLE T1
IF OBJECT_ID('T2') IS NOT NULL DROP TABLE T2
GO
CREATE TABLE T1(ID1 INT,VAL1 VARCHAR(50))
CREATE TABLE T2(ID2 INT,VAL2 VARCHAR(50))
GO
INSERT INTO T1
SELECT 1,'A' UNION ALL
SELECT 2,'B' UNION ALL
SELECT 3,'C'

 

现在我们的目标是让T2表与T1表同步，我直接把完整的MERGE语句帖上来，等下再细说各个部分：

MERGE INTO T2 AS TB_TARGET
USING T1 AS TB_SOURCE
ON TB_TARGET.ID2=TB_SOURCE.ID1
WHEN NOT MATCHED BY TARGET THEN 
INSERT(ID2,VAL2)
VALUES(ID1,VAL1)
WHEN NOT MATCHED BY SOURCE THEN
DELETE
WHEN MATCHED AND TB_TARGET.VAL2<>TB_SOURCE.VAL1 THEN 
UPDATE SET
TB_TARGET.VAL2=TB_SOURCE.VAL1
OUTPUT $ACTION,ISNULL(DELETED.ID2,INSERTED.ID2) AS ID,DELETED.VAL2,INSERTED.VAL2
;

看看MERGE语句输出的结果

/*

$ACTION    ID2         VAL2                                               VAL2
---------- ----------- -------------------------------------------------- --------------------------------------------------
INSERT     1           NULL                                               A
INSERT     2           NULL                                               B
INSERT     3           NULL                                               C

*/

再看一下现在T2的内容：

 

SELECT * FROM T2

/*

ID2         VAL2
------