Sql 中 Merge 的用法

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