本文通过实例演示了如何使用 SQL Server 的 MERGE 语句来同步两个表的数据。通过创建测试环境,展示了如何插入、更新和删除记录,以及 MERGE 语句的详细语法解析,包括 WHEN NOT MATCHED BY 和 WHEN MATCHED 条件。通过 IO 对比,强调了 MERGE 语句在数据同步操作中的效率优势。
摘要由CSDN通过智能技术生成
先建好测试环境:
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
------