/*

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

-----------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------