DML_Data Modification_MERGE
DML_8-Data Modification_MERGE (将Source表合并到Target)
语法:
MERGE INTO 目标表
USING 源表
WHEN MATCHED AND
(Condition) THEN
UPDATE SET
目标表.Field1 = 源表.Field1,
目标表.Field2 = 源表.Field2,
......
WHEN NOT MATCHED
INSERT (Field1, Field2, ...)
VALUES(VALUE1, VALUE2, ...)
WHEN NOT MATCHED BY SOURCE THEN --假使有此逻辑
DELETE... ; --假使需要这么做 (Note: semicolon不能遗漏)
/* MERGE语句的用法 */ ----------------------------------------------------------------------------------------- --表1.Customers use testdb IF OBJECT_ID('dbo.Customers','U') IS NOT NULL DROP TABLE dbo.Customers; CREATE TABLE dbo.Customers ( custid int NOT NULL, companyname VARCHAR(25) NOT NULL, phone VARCHAR(20) NOT NULL, address VARCHAR(50) NOT NULL, CONSTRAINT PK_Customers PRIMARY KEY(custid) ) INSERT INTO dbo.Customers(custid,companyname,phone,address) VALUES (1,'CUST_1','(111) 111-1111','address 1'), (2,'CUST_2','(222) 222-2222','address 2'), (3,'CUST_3','(333) 333-3333','address 3'), (4,'CUST_4','(444) 444-4444','address 4'), (5,'CUST_5','(555) 555-5555','address 5'); ----------------------------------------------------------------------------------------- --表2.CustomersStage IF OBJECT_ID('dbo.CustomersStage','U') IS NOT NULL DROP TABLE dbo.CustomersStage; CREATE TABLE dbo.CustomersStage ( custid int NOT NULL, companyname VARCHAR(25) NOT NULL, phone VARCHAR(20) NOT NULL, address VARCHAR(50) NOT NULL, CONSTRAINT PK_CustomersStage PRIMARY KEY(custid) ) INSERT INTO dbo.CustomersStage(custid,companyname,phone,address) VALUES (2,'AAAAA','(222) 222-2222','address 2'), (3,'CUST_3','(333) 333-3333','address 3'), (5,'BBBBB','CCCCC','DDDDD'), (6,'cust_6 (new)','(666) 666-6666','address 6'), (7,'cust_7 (new)','(777) 777-7777','address 7'); ----------------------------------------------------------------------------------------- SELECT * FROM dbo.Customers SELECT * FROM dbo.CustomersStage ----------------------------------------------------------------------------------------- --3.Merge语句 without And condition MERGE INTO dbo.Customers AS TGT USING dbo.CustomersStage AS SRC ON TGT.custid = SRC.custid WHEN MATCHED THEN --此处没有其他条件,即使来源表与目标表完全相同,也要更新目标表 UPDATE SET TGT.companyname = SRC.companyname, TGT.phone = SRC.phone, TGT.address = SRC.address WHEN NOT MATCHED THEN INSERT (custid, companyname, phone, address) VALUES (SRC.custid,SRC.companyname, SRC.phone, SRC.address); ---------------- Note (此处试验是加了分号结束的,只是为了强调一下必须要以分号结束): Msg 10713, Level 15, State 1, Line 53 A MERGE statement must be terminated by a semi-colon (;). ----------------------------------------------------------------------------------------- ----4.Merge语句 without And condition, WHEN NOT MATCHED BY SOURCE THEN MERGE INTO dbo.Customers AS TGT USING dbo.CustomersStage AS SRC ON TGT.custid = SRC.custid WHEN MATCHED THEN UPDATE SET TGT.companyname = SRC.companyname, TGT.phone = SRC.phone, TGT.address = SRC.address WHEN NOT MATCHED THEN INSERT (custid, companyname, phone, address) VALUES (SRC.custid,SRC.companyname, SRC.phone, SRC.address) WHEN NOT MATCHED BY SOURCE THEN DELETE; ---------------- --在上一次已经合并的基础上,再次进行合并,所以此处显示5行被合并,2行被删除 (7 row(s) affected) ----------------------------------------------------------------------------------------- -- --5.Merge语句 with And condition MERGE INTO dbo.Customers AS TGT USING dbo.CustomersStage AS SRC ON TGT.custid = SRC.custid WHEN MATCHED AND -- (TGT.companyname <> SRC.companyname or TGT.phone <> SRC.phone or TGT.address <> SRC.address) THEN UPDATE SET TGT.companyname = SRC.companyname, TGT.phone = SRC.phone, TGT.address = SRC.address WHEN NOT MATCHED THEN INSERT (custid, companyname, phone, address) VALUES (SRC.custid,SRC.companyname, SRC.phone, SRC.address); ----------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------- -----------------------------------------------------------------------------------------
建立的2个测试表查询的数据:
合并后的数据:
OnionYang@