原文:

　　在实际项目开发过程中，经常有合并数据的需求。这里合并数据的意思是，对于源表A，目标表B，如果A中存在B中不存在则插入记录，如果A中存在B中也存在则更新记录，如果A中不存在B中存在则删除记录。

　　为了实现这一需求，我们有两种解决方案，一是传统的处理方法，即使用EXISTS谓词，更新和新增分开处理的方式。另一种是使用MERGE语句(SQL Server 2008中新增的功能)。为了演示这一功能，首先我们需要准备测试数据，我们在tempdb临时数据库中新建两个表，源表Customers和目标表CustomersStage，然后向这两个表中插入测试数据，如下代码。

USE tempdb;GO-- 合并数据-- 对于源表A，目标表B，如果A中存在B中不存在则插入记录，如果A中存在B中也存在则更新记录，如果A中不存在B中存在则删除记录。-- 准备测试数据IF OBJECT_ID('dbo.Customers','U') IS NOT NULL DROP TABLE dbo.Customers;GOCREATE TABLE dbo.Customers(    custid INT NOT NULL,    companyname NVARCHAR(30) NOT NULL,    phone VARCHAR(30) NOT NULL,    ADDRESS NVARCHAR(50) NOT NULL,    CONSTRAINT PK_Customers PRIMARY KEY(custid)        );INSERT INTO dbo.Customers        ( custid, companyname, phone, ADDRESS )VALUES  (1,N'cust 1','(111)111-111',N'address 1'),        (2,N'cust 2','(222)222-222',N'address 2'),        (3,N'cust 3','(333)333-333',N'address 3'),        (4,N'cust 4','(444)444-444',N'address 4'),        (5,N'cust 5','(555)555-555',N'address 5');        IF OBJECT_ID('dbo.CustomersStage','U') IS NOT NULL DROP TABLE dbo.CustomersStage;GOCREATE TABLE dbo.CustomersStage(    custid INT NOT NULL,    companyname NVARCHAR(30) NOT NULL,    phone VARCHAR(30) NOT NULL,    ADDRESS NVARCHAR(50) NOT NULL,    CONSTRAINT PK_CustomersStage PRIMARY KEY(custid)    );INSERT INTO dbo.CustomersStage        ( custid, companyname, phone, ADDRESS )VALUES  (2,N'AAAAA','(222)222-222',N'address 2'),        (3,N'cust 3','(333)333-333',N'address 3'),        (5,N'BBBBB','CCCCC',N'DDDDD'),        (6,N'cust 6(new)','(666)666-666',N'address 6'),        (7,N'cust 7(new)','(777)777-777',N'address 7');

运行以下代码查看示例数据效果。

SELECT * FROM dbo.Customers;SELECT * FROM dbo.CustomersStage;

查询结果如下。

基于以上测试数据，所以我们要做的是，更新客户2，3和5的信息，将源表中客户6和7插入到目标表中，并且删除目标表中客户1和4。

首先使用传统的处理方式，既使用临时表和EXISTS谓词，如下代码所示。

-- 方法二：使用临时表和EXISTS谓词-- 将源表的主键custid插入到临时表中BEGIN TRAN;IF OBJECT_ID('tempdb.dbo.#CustomersStage','U') IS NOT NULL DROP TABLE dbo.#CustomersStage;GOSELECT custid INTO #CustomersStage FROM dbo.CustomersStage;DECLARE @custid INT;WHILE EXISTS (SELECT * FROM #CustomersStage)BEGIN    SET @custid= (SELECT TOP 1 custid FROM #CustomersStage ORDER BY custid ASC);        -- 方法1，if row exists update,otherwise insert    IF EXISTS (SELECT * FROM dbo.Customers WHERE custid= @custid)    BEGIN        -- 更新        UPDATE customers SET companyname= customersStage.companyname,phone= customersStage.phone,ADDRESS= customersStage.ADDRESS        FROM dbo.Customers AS customers        LEFT JOIN dbo.CustomersStage AS customersStage ON customers.custid = customersStage.custid        WHERE customersStage.custid= @custid;    END    ELSE    BEGIN        -- 插入        INSERT INTO dbo.Customers                ( custid, companyname, phone, ADDRESS )        SELECT custid, companyname,phone,ADDRESS FROM dbo.CustomersStage        WHERE custid=@custid;    END        -- 方法2，update,if @@ROWCOUNT=0 then insert    --UPDATE customers SET companyname= customersStage.companyname,phone= customersStage.phone,ADDRESS= customersStage.ADDRESS    --FROM dbo.Customers AS customers    --LEFT JOIN dbo.CustomersStage AS customersStage ON customers.custid = customersStage.custid    --WHERE customersStage.custid= @custid;        --IF @@ROWCOUNT=0    --BEGIN    --    -- 插入    --    INSERT INTO dbo.Customers    --            ( custid, companyname, phone, ADDRESS )    --    SELECT custid, companyname,phone,ADDRESS FROM dbo.CustomersStage    --    WHERE custid=@custid;        --END        DELETE #CustomersStage    WHERE custid= @custid;ENDGO-- 从目标表中删除在源表中不存在的行IF OBJECT_ID('tempdb.dbo.#Customers','U') IS NOT NULL DROP TABLE dbo.#Customers;GOSELECT custidINTO #CustomersFROM dbo.Customers;DECLARE @custidTGT INT;WHILE EXISTS (SELECT * FROM #Customers)BEGIN    SET @custidTGT= (SELECT TOP 1 custid FROM #Customers ORDER BY custid ASC);        IF NOT EXISTS (SELECT * FROM dbo.CustomersStage WHERE custid= @custidTGT)    BEGIN        DELETE FROM dbo.Customers        WHERE custid= @custidTGT;            END        DELETE #Customers    WHERE custid= @custidTGT;ENDGOSELECT * FROM dbo.Customers;ROLLBACK TRAN;

处理结果如下。

从目标表的查询结果可以看到，客户2，3和5的信息已被更新，新的客户6和7已经插入，在源表中不存在的客户1和4已经被删除了，所以已经实现了我们的需求。但是，传统的处理方法一是代码量大，而且每操作一行数据需要两次查询数据库，导致效率较低。为了解决这些问题，我们可以使用新引入的MERGE语句来实现这个功能，代码如下。

-- 方法一：使用MERGE语句BEGIN TRAN;MERGE INTO dbo.Customers AS tgtUSING dbo.CustomersStage AS src ON tgt.custid=src.custidWHEN MATCHED AND     ( (tgt.companyname<>src.companyname         OR (tgt.companyname IS NOT NULL AND src.companyname IS NULL)        OR (tgt.companyname IS NULL AND src.companyname IS NOT NULL))    OR (tgt.phone<>src.phone        OR (tgt.phone IS NOT NULL AND src.phone IS NULL)        OR (tgt.phone IS NULL AND src.phone IS NOT NULL))    OR (tgt.ADDRESS<>src.ADDRESS        OR (tgt.ADDRESS IS NOT NULL AND src.ADDRESS IS NULL)        OR (tgt.ADDRESS IS NULL AND src.ADDRESS IS NOT NULL)) ) THEN     UPDATE SET tgt.companyname= src.companyname,                tgt.phone= src.phone,                tgt.ADDRESS= src.ADDRESSWHEN NOT MATCHED THEN     INSERT (custid,companyname,phone,ADDRESS)    VALUES (src.custid,src.companyname,src.phone,src.ADDRESS)WHEN NOT MATCHED BY SOURCE THEN     DELETE;    SELECT * FROM dbo.Customers;ROLLBACK TRAN;

通过以上查询代码我们会发现，我们使用了更少的代码实现了相同的功能，而且逻辑更清晰易懂。

附：全部sql代码。

USE tempdb;GO-- 合并数据-- 对于源表A，目标表B，如果A中存在B中不存在则插入记录，如果A中存在B中也存在则更新记录，如果A中不存在B中存在则删除记录。-- 准备测试数据IF OBJECT_ID('dbo.Customers','U') IS NOT NULL DROP TABLE dbo.Customers;GOCREATE TABLE dbo.Customers(    custid INT NOT NULL,    companyname NVARCHAR(30) NOT NULL,    phone VARCHAR(30) NOT NULL,    ADDRESS NVARCHAR(50) NOT NULL,    CONSTRAINT PK_Customers PRIMARY KEY(custid)        );INSERT INTO dbo.Customers        ( custid, companyname, phone, ADDRESS )VALUES  (1,N'cust 1','(111)111-111',N'address 1'),        (2,N'cust 2','(222)222-222',N'address 2'),        (3,N'cust 3','(333)333-333',N'address 3'),        (4,N'cust 4','(444)444-444',N'address 4'),        (5,N'cust 5','(555)555-555',N'address 5');        IF OBJECT_ID('dbo.CustomersStage','U') IS NOT NULL DROP TABLE dbo.CustomersStage;GOCREATE TABLE dbo.CustomersStage(    custid INT NOT NULL,    companyname NVARCHAR(30) NOT NULL,    phone VARCHAR(30) NOT NULL,    ADDRESS NVARCHAR(50) NOT NULL,    CONSTRAINT PK_CustomersStage PRIMARY KEY(custid)    );INSERT INTO dbo.CustomersStage        ( custid, companyname, phone, ADDRESS )VALUES  (2,N'AAAAA','(222)222-222',N'address 2'),        (3,N'cust 3','(333)333-333',N'address 3'),        (5,N'BBBBB','CCCCC',N'DDDDD'),        (6,N'cust 6(new)','(666)666-666',N'address 6'),        (7,N'cust 7(new)','(777)777-777',N'address 7');        -- 方法一：使用MERGE语句BEGIN TRAN;MERGE INTO dbo.Customers AS tgtUSING dbo.CustomersStage AS src ON tgt.custid=src.custidWHEN MATCHED AND     ( (tgt.companyname<>src.companyname         OR (tgt.companyname IS NOT NULL AND src.companyname IS NULL)        OR (tgt.companyname IS NULL AND src.companyname IS NOT NULL))    OR (tgt.phone<>src.phone        OR (tgt.phone IS NOT NULL AND src.phone IS NULL)        OR (tgt.phone IS NULL AND src.phone IS NOT NULL))    OR (tgt.ADDRESS<>src.ADDRESS        OR (tgt.ADDRESS IS NOT NULL AND src.ADDRESS IS NULL)        OR (tgt.ADDRESS IS NULL AND src.ADDRESS IS NOT NULL)) ) THEN     UPDATE SET tgt.companyname= src.companyname,                tgt.phone= src.phone,                tgt.ADDRESS= src.ADDRESSWHEN NOT MATCHED THEN     INSERT (custid,companyname,phone,ADDRESS)    VALUES (src.custid,src.companyname,src.phone,src.ADDRESS)WHEN NOT MATCHED BY SOURCE THEN     DELETE;    SELECT * FROM dbo.Customers;ROLLBACK TRAN;-- 方法二：使用临时表和EXISTS谓词-- 将源表的主键custid插入到临时表中BEGIN TRAN;IF OBJECT_ID('tempdb.dbo.#CustomersStage','U') IS NOT NULL DROP TABLE dbo.#CustomersStage;GOSELECT custid INTO #CustomersStage FROM dbo.CustomersStage;DECLARE @custid INT;WHILE EXISTS (SELECT * FROM #CustomersStage)BEGIN    SET @custid= (SELECT TOP 1 custid FROM #CustomersStage ORDER BY custid ASC);        -- 方法1，if row exists update,otherwise insert    IF EXISTS (SELECT * FROM dbo.Customers WHERE custid= @custid)    BEGIN        -- 更新        UPDATE customers SET companyname= customersStage.companyname,phone= customersStage.phone,ADDRESS= customersStage.ADDRESS        FROM dbo.Customers AS customers        LEFT JOIN dbo.CustomersStage AS customersStage ON customers.custid = customersStage.custid        WHERE customersStage.custid= @custid;    END    ELSE    BEGIN        -- 插入        INSERT INTO dbo.Customers                ( custid, companyname, phone, ADDRESS )        SELECT custid, companyname,phone,ADDRESS FROM dbo.CustomersStage        WHERE custid=@custid;    END        -- 方法2，update,if @@ROWCOUNT=0 then insert    --UPDATE customers SET companyname= customersStage.companyname,phone= customersStage.phone,ADDRESS= customersStage.ADDRESS    --FROM dbo.Customers AS customers    --LEFT JOIN dbo.CustomersStage AS customersStage ON customers.custid = customersStage.custid    --WHERE customersStage.custid= @custid;        --IF @@ROWCOUNT=0    --BEGIN    --    -- 插入    --    INSERT INTO dbo.Customers    --            ( custid, companyname, phone, ADDRESS )    --    SELECT custid, companyname,phone,ADDRESS FROM dbo.CustomersStage    --    WHERE custid=@custid;        --END        DELETE #CustomersStage    WHERE custid= @custid;ENDGO-- 从目标表中删除在源表中不存在的行IF OBJECT_ID('tempdb.dbo.#Customers','U') IS NOT NULL DROP TABLE dbo.#Customers;GOSELECT custidINTO #CustomersFROM dbo.Customers;DECLARE @custidTGT INT;WHILE EXISTS (SELECT * FROM #Customers)BEGIN    SET @custidTGT= (SELECT TOP 1 custid FROM #Customers ORDER BY custid ASC);        IF NOT EXISTS (SELECT * FROM dbo.CustomersStage WHERE custid= @custidTGT)    BEGIN        DELETE FROM dbo.Customers        WHERE custid= @custidTGT;            END        DELETE #Customers    WHERE custid= @custidTGT;ENDGOSELECT * FROM dbo.Customers;ROLLBACK TRAN;