Sql 2008 中 Merge 的用法
Merge 是Sql2008 新增命令之一,它可以基于两表连接的某个条件的结果操作数据 (INSERT, UPDATE, and DELETE), 它的好处是少写一些代码,多做一些事情, 以下我们将充分说明这个优点。
有这样一个需求:
USE tempdb;
IF OBJECT_ID('dbo.Customers', 'U') IS NOT NULL DROP TABLE dbo.Customers;
GO
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');
IF OBJECT_ID('dbo.CustomersStage', 'U') IS NOT NULL DROP TABLE dbo.CustomersStage;
GO
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');
要求将dbo.CustomersStage中的数据合并入dbo.Customers,合并规则如下:
a. dbo.Customers 与dbo.CustomersStage 都有的一模一样的数据在dbo.Customers中保持不变
b. dbo.Customers 与dbo.CustomersStage 中custid 相同的则按照dbo.CustomersStage中的companyname, phone, address数据更新对应的dbo.Customers数据
c. 不存在于dbo.Customers(custid)中,但存在于dbo.CustomersStage(custid)的数据要插入dbo.Customers
大家也许可以用多条Sql来解决:
规则a 不需要操作
Sql for 规则b:
Update C
set C.companyname = cs.companyname,
C.phone = cs.phone,
C.address =cs.address
from dbo.Customers as C
inner join dbo.CustomersStage CS
on CS.custid = C.custid
Sql for 规则c:
insert into
dbo.Customers(custid, companyname, phone, address)
select custid, companyname, phone, address
from dbo.CustomersStage where custid not in
(select custid from dbo.Customers)
下面看看使用Merge解决这个问题的语句:
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);
如果再加一条规则d:删除dbo.Customers中custid 不在dbo.CustomersStage中的记录
如果不用merge 就要再加一句:
delete from dbo.Customers
where custid not in
(select custid from dbo.CustomersStage)
但如果用merge,注意高亮部分是为规则d加的:
MERGE 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;
但是这里有个问题,当custid在两个表中匹配上时,无论对应的记录是否一致,以上语句都是要更新dbo.Customers的,这样如果相同的很多,不起作用的update就浪费了很多资源,解决的办法(高亮部分)是:
MERGE 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);