用SQL2008的MERGE语句同步表

业务需要同步两表,就研究了一下2008的新功能,感觉还是不错的,怎么说也是系统的功能,同样的作用应该比自己手动做的好。因为我是从没用过到刚会用,所以也只写这部分的心得,再深的请看专家们的BLOG。

 

先建好测试环境:

 

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。

第二三行和普通的插入语句差不多,区别就在于没有目标表名和只能用VALUES不能用SELECT,因为这里都是针对单行的操作。

5.

WHEN NOT MATCHED BY SOURCE THEN
DELETE

这个就简单了,如果是原表找不到新表的匹配记录,就把新表的删了。需要注意的就是如果要加上这句,上面的NOT MATCHED必须加BY TARGET。

6.

WHEN MATCHED AND TB_TARGET.VAL2<>TB_SOURCE.VAL1 THEN
UPDATE SET
TB_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也是可以加TOP的,在第一行的MERGE INTO中间加就行了,不过我觉得比较鸡肋,谁闲着没事就同步一两条的。唯一我能想到的用处就是大数据量同步时分批执行,每次同步N行。

 

最后简单对比一下MERGE和原本同样效果的操作的IO对比

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
;

/*
表 '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,VAL1
FROM T1 WHERE NOT EXISTS(
SELECT 1 FROM T2 WHERE T2.ID2=T1.ID1
)

UPDATE T2
SET T2.VAL2=T1.VAL1
FROM T2
INNER JOIN T1 ON T2.ID2=T1.ID1
AND T2.VAL2<>T1.VAL1

DELETE 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 次。
*/

 

如果有不对或者看不明白的地方,直接留言就好。

 

以上。

  • 0
    点赞
  • 12
    评论
  • 0
    收藏
  • 一键三连
    一键三连
  • 扫一扫,分享海报

打赏
文章很值,打赏犒劳作者一下
相关推荐
<p> <span style="font-size:14px;color:#337FE5;">【为什么学爬虫?】</span> </p> <p> <span style="font-size:14px;">       1、爬虫入手容易,但是深入较难,如何写出高效率的爬虫,如何写出灵活性高可扩展的爬虫都是一项技术活。另外在爬虫过程中,经常容易遇到被反爬虫,比如字体反爬、IP识别、验证码等,如何层层攻克难点拿到想要的数据,这门课程,你都能学到!</span> </p> <p> <span style="font-size:14px;">       2、如果是作为一个其他行业的开发者,比如app开发,web开发,学习爬虫能让你加强对技术的认知,能够开发出更加安全的软件和网站</span> </p> <p> <br /> </p> <span style="font-size:14px;color:#337FE5;">【课程设计】</span> <p class="ql-long-10663260"> <span> </span> </p> <p class="ql-long-26664262" style="font-size:11pt;color:#494949;"> 一个完整的爬虫程序,无论大小,总体来说可以分成三个步骤,分别是: </p> <ol> <li class="" style="font-size:11pt;color:#494949;"> 网络请求:模拟浏览器的行为从网上抓取数据。 </li> <li class="" style="font-size:11pt;color:#494949;"> 数据解析:将请求下来的数据进行过滤,提取我们想要的数据。 </li> <li class="" style="font-size:11pt;color:#494949;"> 数据存储:将提取到的数据存储到硬盘或者内存中。比如用mysql数据库或者redis等。 </li> </ol> <p class="ql-long-26664262" style="font-size:11pt;color:#494949;"> 那么本课程也是按照这几个步骤循序渐进的进行讲解,带领学生完整的掌握每个步骤的技术。另外,因为爬虫的多样性,在爬取的过程中可能会发生被反爬、效率低下等。因此我们又增加了两个章节用来提高爬虫程序的灵活性,分别是: </p> <ol> <li class="" style="font-size:11pt;color:#494949;"> 爬虫进阶:包括IP代理,多线程爬虫,图形验证码识别、JS加密解密、动态网页爬虫、字体反爬识别等。 </li> <li class="" style="font-size:11pt;color:#494949;"> Scrapy和分布式爬虫:Scrapy框架、Scrapy-redis组件、分布式爬虫等。 </li> </ol> <p class="ql-long-26664262" style="font-size:11pt;color:#494949;"> 通过爬虫进阶的知识点我们能应付大量的反爬网站,而Scrapy框架作为一个专业的爬虫框架,使用他可以快速提高我们编写爬虫程序的效率和速度。另外如果一台机器不能满足你的需求,我们可以用分布式爬虫让多台机器帮助你快速爬取数据。 </p> <p style="font-size:11pt;color:#494949;">   </p> <p class="ql-long-26664262" style="font-size:11pt;color:#494949;"> 从基础爬虫到商业化应用爬虫,本套课程满足您的所有需求! </p> <p class="ql-long-26664262" style="font-size:11pt;color:#494949;"> <br /> </p> <p> <br /> </p> <p> <span style="font-size:14px;background-color:#FFFFFF;color:#337FE5;">【课程服务】</span> </p> <p> <span style="font-size:14px;">专属付费社群+定期答疑</span> </p> <p> <br /> </p> <p class="ql-long-24357476"> <span style="font-size:16px;"><br /> </span> </p> <p> <br /> </p> <p class="ql-long-24357476"> <span style="font-size:16px;"></span> </p>
©️2020 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页

打赏

guguda2008

你的鼓励将是我创作的最大动力

¥2 ¥4 ¥6 ¥10 ¥20
输入1-500的整数
余额支付 (余额:-- )
扫码支付
扫码支付:¥2
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值