读书笔记之MERGE 语句使用

常用语法

MERGE INTO <target table> AS TGT
USING <SOURCE TABLE> AS SRC
  ON <merge predicate>
WHEN MATCHED [AND <predicate>]                  -- 允许两个子句:
  THEN <action>                                 -- UPDATE 和 DELETE
WHEN NOT MATCHED [BY TARGET] [AND <predicate>]  -- 允许一个子句:
  THEN INSERT...                                –- INSERT
WHEN NOT MATCHED BY SOURCE [AND <predicate>]    -- 允许两个子句:
  THEN <action>;                                -- UPDATE 和 DELETE

建立目标表

-- clear table and reset sequence if the already exist
--TRUNCATE TABLE Sales.MyOrders;
--ALTER SEQUENCE Sales.SeqOrderIDs RESTART WITH 1;

-- create table and sequence if they don't already exist
IF OBJECT_ID(N'Sales.MyOrders', N'U') IS NOT NULL DROP TABLE Sales.MyOrders;
IF OBJECT_ID(N'Sales.SeqOrderIDs', N'SO') IS NOT NULL DROP SEQUENCE Sales.SeqOrderIDs;

CREATE SEQUENCE Sales.SeqOrderIDs AS INT
  MINVALUE 1
  CYCLE;

CREATE TABLE Sales.MyOrders
(
  orderid INT NOT NULL
    CONSTRAINT PK_MyOrders_orderid PRIMARY KEY
    CONSTRAINT DFT_MyOrders_orderid
      DEFAULT(NEXT VALUE FOR Sales.SeqOrderIDs),
  custid  INT NOT NULL
    CONSTRAINT CHK_MyOrders_custid CHECK(custid > 0),
  empid   INT NOT NULL
    CONSTRAINT CHK_MyOrders_empid CHECK(empid > 0),
  orderdate DATE NOT NULL
);

源表例子，这里使用参数作为数据源。

-- SELECT without FROM
DECLARE
  @orderid   AS INT  = 1,
  @custid    AS INT  = 1,
  @empid     AS INT  = 2,
  @orderdate AS DATE = '20120620';

SELECT *
FROM (SELECT @orderid, @custid, @empid, @orderdate )
      AS SRC( orderid,  custid,  empid,  orderdate );
GO
      
-- table value constructor
DECLARE
  @orderid   AS INT  = 1,
  @custid    AS INT  = 1,
  @empid     AS INT  = 2,
  @orderdate AS DATE = '20120620';

SELECT *
FROM (VALUES(@orderid, @custid, @empid, @orderdate))
      AS SRC( orderid,  custid,  empid,  orderdate);

实际例子

例1 ，很多人用下面这段来更新仓库中的表（更新存在的，插入不存在的）

-- update where exists (only if different), insert where not exists,
-- delete when exists in target but not in source
DECLARE
  @orderid   AS INT  = 1,
  @custid    AS INT  = 1,
  @empid     AS INT  = 2,
  @orderdate AS DATE = '20120620';

MERGE INTO Sales.MyOrders WITH (HOLDLOCK) AS TGT          --这里 HOLDLOCK或者SERIALIZABLE作用都是一样的，防止MERGE冲突
USING (VALUES(@orderid, @custid, @empid, @orderdate))        --比如某个主键ID不存在于目标表。有两个进程P1和P2使用MERGE同时处理这个ID
       AS SRC( orderid,  custid,  empid,  orderdate)       --P1插入了这个ID的同时，P2也插入该ID，此时P2就会因违背主键约束而失败。
  ON SRC.orderid = TGT.orderid
--WHEN MATCHED THEN UPDATE                                  --这里有一个性能问题，如果第二次执行该语句，碰到ID一模一样数据，会再一次更新。
WHEN MATCHED AND (   TGT.custid    <> SRC.custid           --这样会损耗性能，可以额外加判断来减少性能损失。
                  OR TGT.empid     <> SRC.empid
                  OR TGT.orderdate <> SRC.orderdate) THEN UPDATE  
  SET TGT.custid    = SRC.custid,     
      TGT.empid     = SRC.empid,
      TGT.orderdate = SRC.orderdate
WHEN NOT MATCHED THEN INSERT                              --如果目标表不存在则插入
  VALUES(SRC.orderid, SRC.custid, SRC.empid, SRC.orderdate);
WHEN NOT MATCHED BY SOURCE THEN                           --如果目标表存在但源表不存在则删除
  DELETE;
OUTPUT
  $action AS the_action,
  COALESCE(inserted.orderid, deleted.orderid) AS orderid

注意，有时候还需要处理NULL值

TGT.custid = SRC.custid OR (TGT.custid IS NULL AND SRC.custid IS NOT
NULL) OR (TGT.custid IS NOT NULL AND SRC.custid IS NULL).

例2 ，注意ON 子句规则

MERGE INTO Sales.MyOrders AS TGT
USING Sales.Orders AS SRC
  ON  SRC.orderid = TGT.orderid
  AND shipcountry = N'Norway'
WHEN MATCHED AND (   TGT.custid    <> SRC.custid
                  OR TGT.empid     <> SRC.empid
                  OR TGT.orderdate <> SRC.orderdate) THEN UPDATE
  SET TGT.custid    = SRC.custid,
      TGT.empid     = SRC.empid,
      TGT.orderdate = SRC.orderdate
WHEN NOT MATCHED THEN INSERT
  VALUES(SRC.orderid, SRC.custid, SRC.empid, SRC.orderdate);

以上代码， ON子句有一个谓语 shipcountry = N'Norway' ，当第一次顺利执行。但是第二次就会报错。

之所以报错，是因为ON字句并不会过滤数据，如果shipcountry 不是Norway，则直接执行 NOT MATCHED ，此时因为目标表里面已经有了数据，导致了主键约束错误

解决方法只能事先过滤，然后再执行MERGE

-- 使用CTE
WITH SRC AS
(
  SELECT *
  FROM Sales.Orders
  WHERE shipcountry = N'Norway'
)
MERGE INTO Sales.MyOrders AS TGT
USING SRC
  ON  SRC.orderid = TGT.orderid 
WHEN MATCHED AND (   TGT.custid    <> SRC.custid
                  OR TGT.empid     <> SRC.empid
                  OR TGT.orderdate <> SRC.orderdate) THEN UPDATE
  SET TGT.custid    = SRC.custid,
      TGT.empid     = SRC.empid,
      TGT.orderdate = SRC.orderdate
WHEN NOT MATCHED THEN INSERT
  VALUES(SRC.orderid, SRC.custid, SRC.empid, SRC.orderdate);

-- 使用派生表
MERGE INTO Sales.MyOrders AS TGT
USING ( SELECT *
        FROM Sales.Orders
        WHERE shipcountry = N'Norway' )
 AS SRC
  ON  SRC.orderid = TGT.orderid 
WHEN MATCHED AND (   TGT.custid    <> SRC.custid
                  OR TGT.empid     <> SRC.empid
                  OR TGT.orderdate <> SRC.orderdate) THEN UPDATE
  SET TGT.custid    = SRC.custid,
      TGT.empid     = SRC.empid,
      TGT.orderdate = SRC.orderdate
WHEN NOT MATCHED THEN INSERT
  VALUES(SRC.orderid, SRC.custid, SRC.empid, SRC.orderdate);

例3 XML处理

SELECT  ProductID ,
        Name
INTO    Products
FROM    Production.Product

SELECT  ProductID AS "@id" ,
        Name AS "@name"
FROM    Products
WHERE   Name LIKE '_A%'
FOR     XML PATH('product') ,
            ROOT('products');

DECLARE @Xml XML = N'
<products>
  <product id="843" name="Cable Lock" />
  <product id="873" name="Patch Kit/8 P11atches" />
  <product id="875" delete="true" name="Racing Socks, L" />
  <product id="874" name="Racing Socks, M" />
  <product id="846" name="Taillights - Battery-Pow1ered" />
  <product name="Wdsfe - 30 oz." />
</products>';
WITH    src
          AS ( SELECT   xt.xc.value('@id', 'INT') AS ProductID ,
                        xt.xc.value('@name', 'NVARCHAR(1000)') AS Name ,
                        ISNULL(xt.xc.value('@delete', 'BIT'), 0) AS DoDelete
               FROM     @Xml.nodes('/products/product') AS xt ( xc )
             )
    MERGE INTO Products AS dest
    USING src
    ON src.ProductID = dest.ProductID
    WHEN NOT MATCHED THEN
        INSERT ( Name )
        VALUES ( src.Name )
    WHEN MATCHED AND src.DoDelete = 0 THEN
        UPDATE SET
               Name = src.Name
    WHEN MATCHED AND src.DoDelete = 1 THEN
        DELETE ;