使用MERGE语句同步表

先建好测试环境： USE TEMPDBGOIF OBJECT_ID('T1') IS NOT NULL DROP TABLE T1IF OBJECT_ID('T2') IS NOT NULL DROP TABLE T2GOCREATE TABLE T1(ID1 INT,VAL1 VARCHAR(50))CREATE TABLE T2(ID2 INT,VAL2 VARCHAR(50))GOINSERT INTO T1SELECT 1,'A' UNION ALLSELECT 2,'B' UNION ALLSELECT 3,'C' 现在我们的目标是让T2表与T1表同步，我直接把完整的MERGE语句帖上来，等下再细说各个部分：

MERGE INTO T2 AS TB_TARGETUSING T1 AS TB_SOURCEON TB_TARGET.ID2=TB_SOURCE.ID1WHEN NOT MATCHED BY TARGET THEN INSERT(ID2,VAL2)VALUES(ID1,VAL1)WHEN NOT MATCHED BY SOURCE THENDELETEWHEN MATCHED AND TB_TARGET.VAL2<>TB_SOURCE.VAL1 THEN UPDATE SETTB_TARGET.VAL2=TB_SOURCE.VAL1OUTPUT $ACTION,ISNULL(DELETED.ID2,INSERTED.ID2) AS ID,DELETED.VAL2,INSERTED.VAL2;看看MERGE语句输出的结果/*$ACTION    ID2         VAL2                                               VAL2---------- ----------- -------------------------------------------------- --------------------------------------------------INSERT     1           NULL                                               AINSERT     2           NULL                                               BINSERT     3           NULL                                               C*/再看一下现在T2的内容： SELECT * FROM T2/*ID2         VAL2----------- --------------------------------------------------1           A2           B3           C*/ 可以看到T1的东东已经过去了，也就是说初步的同步完成了。现在做一些其它的操作，我们分别插入、更新、删除一条数据：UPDATE T1 SET VAL1='D' WHERE ID1=3DELETE FROM T1 WHERE ID1=2INSERT INTO T1SELECT 4,'E'SELECT * FROM T1 /*ID1         VAL1----------- --------------------------------------------------1           A4           E3           D*/现在各种数据都有了，1没变，2删了，3改了，4是加的。再运行上面那坨MERGE语句：MERGE INTO T2 AS TB_TARGETUSING T1 AS TB_SOURCEON TB_TARGET.ID2=TB_SOURCE.ID1WHEN NOT MATCHED BY TARGET THEN INSERT(ID2,VAL2)VALUES(ID1,VAL1)WHEN NOT MATCHED BY SOURCE THENDELETEWHEN MATCHED AND TB_TARGET.VAL2<>TB_SOURCE.VAL1 THEN UPDATE SETTB_TARGET.VAL2=TB_SOURCE.VAL1OUTPUT $ACTION,ISNULL(DELETED.ID2,INSERTED.ID2) AS ID,DELETED.VAL2,INSERTED.VAL2;/*$ACTION    ID          VAL2                                               VAL2---------- ----------- -------------------------------------------------- --------------------------------------------------INSERT     4           NULL                                               EDELETE     2           B                                                  NULLUPDATE     3           C                                                  D*/ 看一下T2的数据SELECT * FROM T2/*ID2         VAL2----------- --------------------------------------------------1           A3           D4           E*/可以看到，数据已经完全同步了。看到效果后，我们就可以开始说正文了，我再粘一次MERGE语句，然后一句一句细说MERGE INTO T2 AS TB_TARGETUSING T1 AS TB_SOURCEON TB_TARGET.ID2=TB_SOURCE.ID1WHEN NOT MATCHED BY TARGET THEN INSERT(ID2,VAL2)VALUES(ID1,VAL1)WHEN NOT MATCHED BY SOURCE THENDELETEWHEN MATCHED AND TB_TARGET.VAL2<>TB_SOURCE.VAL1 THEN UPDATE SETTB_TARGET.VAL2=TB_SOURCE.VAL1OUTPUT $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 THENINSERT(ID2,VAL2)VALUES(ID1,VAL1)这里放到一起说。看到INSERT应该就能猜这段语句的意思是“如果原表有的记录新表没有，就插入”。NOT MATCHED表示不匹配， BY TARGET表示是新表找不到匹配原表条件（就是上面的ON后写的）的记录， BY TARGET 可以不写，默认就是BY TARGET，但如果要写两个WHEN MATCHED就必须要写，比如上面这个MERGE。第二三行和普通的插入语句差不多，区别就在于没有目标表名和只能用VALUES不能用SELECT，因为这里都是针对单行的操作。5.WHEN NOT MATCHED BY SOURCE THENDELETE这个就简单了，如果是原表找不到新表的匹配记录，就把新表的删了。需要注意的就是如果要加上这句，上面的NOT MATCHED必须加BY TARGET。6.WHEN MATCHED AND TB_TARGET.VAL2<>TB_SOURCE.VAL1 THEN UPDATE SETTB_TARGET.VAL2=TB_SOURCE.VAL1第一行后面的AND部分可以不要，相当于更新的另一个匹配条件，像上面例子中，ID为1的那条数据没有动，但因为能找到匹配记录还是会更新，加上条件就可以避免这种无效操作了。7.OUTPUT $ACTION,ISNULL(DELETED.ID2,INSERTED.ID2) AS ID,DELETED.VAL2,INSERTED.VAL2这行可以都去掉，作用就是输出同步的数据，用过触发器的同学对INSERTED和DELETED两个表应该灰常熟悉，分别放的是更新后的值和更新前的值，看看最后一次MERGE输出的信息就能差不多看出门道了，我就不多说了。如果要调试语句的话，可以加上这句，正常的同步就可以去掉了。8.;这个必须有。。。。。 总之，4，5，6，7都是可以去掉的，但4，5，6至少要有一个，这就是MERGE的全部常用语法了。还有一个最后可以加 OPTION查询提示  最后简单对比一下MERGE和原本同样效果的操作的IO对比MERGE INTO T2 AS TB_TARGETUSING T1 AS TB_SOURCEON TB_TARGET.ID2=TB_SOURCE.ID1WHEN NOT MATCHED BY TARGET THEN INSERT(ID2,VAL2)VALUES(ID1,VAL1)WHEN NOT MATCHED BY SOURCE THENDELETEWHEN MATCHED AND TB_TARGET.VAL2<>TB_SOURCE.VAL1 THEN UPDATE SETTB_TARGET.VAL2=TB_SOURCE.VAL1OUTPUT $ACTION,ISNULL(DELETED.ID2,INSERTED.ID2) AS ID,DELETED.VAL2,INSERTED.VAL2; /*表 'T2'。扫描计数 2，逻辑读取 7 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。表 'T1'。扫描计数 2，逻辑读取 4 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。*/ PRINT '------------------------------------------------------------------------------------'INSERT INTO T2(ID2,VAL2)SELECT ID1,VAL1FROM T1 WHERE NOT EXISTS(SELECT 1 FROM T2 WHERE T2.ID2=T1.ID1)UPDATE T2SET T2.VAL2=T1.VAL1FROM T2INNER JOIN T1 ON T2.ID2=T1.ID1AND T2.VAL2<>T1.VAL1DELETE FROM T2 WHERE NOT EXISTS(SELECT 1 FROM T1 WHERE T1.ID1=T2.ID2) /*表 'T2'。扫描计数 1，逻辑读取 4 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。表 'Worktable'。扫描计数 1，逻辑读取 5 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。表 'T1'。扫描计数 1，逻辑读取 1 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。表 'T2'。扫描计数 1，逻辑读取 2 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。表 'T1'。扫描计数 1，逻辑读取 4 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。表 'T2'。扫描计数 1，逻辑读取 1 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。表 'T1'。扫描计数 1，逻辑读取 4 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。*/