先建好测试环境：
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
----------- --------------------------------------------------
1 A
2 B
3 C
*/
可以看到T1的东东已经过去了，也就是说初步的同步完成了。
现在做一些其它的操作，我们分别插入、更新、删除一条数据：
UPDATE T1 SET VAL1='D' WHERE ID1=3
DELETE FROM T1 WHERE ID1=2
INSERT INTO T1
SELECT 4,'E'
SELECT * FROM T1
/*
ID1 VAL1
----------- --------------------------------------------------
1 A
4 E
3 D
*/
现在各种数据都有了，1没变，2删了，3改了，4是加的。再运行上面那坨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
;
/*
$ACTION ID VAL2 VAL2
---------- ----------- -------------------------------------------------- --------------------------------------------------
INSERT 4 NULL E
DELETE 2 B NULL
UPDATE 3 C D
*/
看一下T2的数据
SELECT * FROM T2
/*
ID2 VAL2
----------- --------------------------------------------------
1 A
3 D
4 E
*/
可以看到，数据已经完全同步了。看到效果后，我们就可以开始说正文了，我再粘一次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
;
1.
MERGE INTO T2 AS TB_TARGET
指定要同步的目标表。MERGE是关键字，INTO可有可无，T2是目标表名，AS可有可无，TB_TARGET是表别名。
如果要对目标表加表提示和索引提示，比如WITH(...)，加在T2和AS中间就可以了。
2.
USING T1 AS TB_SOURCE
指定用来作为同步源的表或其它东东。USING是关键字，T1是原表名或一个子查询，比如一堆JOIN出来的东西用括号括起来。
AS同上，TB_SOURCE是别名。
3.
ON TB_TARGET.ID2=TB_SOURCE.ID1
关联条件，没什么好说的，注意这里开始就用到上面定义的别名了。
4.
WHEN NOT MATCHED BY TARGET THEN
INSERT(ID2,VAL2)
VALUES(ID1,VAL1)
这里放到一起说。看到INSERT应该就能猜这段语句的意思是“如果原表有的记录新表没有，就插入”。
NOT MATCHED表示不匹配， BY TARGET表示是新表找不到匹配原表条件（就是上面的ON后写的）的记录， BY TARGET 可以不写，默认就是BY TARGET，但如果要写两个WHEN MATCHED就必须要写，比如上面这个MERGE。
第二三行和普通的插入语句差不多，区别就在于没有目标表名和只