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);
