This website requires JavaScript.

MSSQL 读书笔记之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' ,当第一次顺利执行。但是第二次就会报错。
[![image](http://images.cnitblog.com/blog/378031/201503/122312535279975.png "image")](http://images.cnitblog.com/blog/378031/201503/122312527304333.png)

之所以报错,是因为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 ;
 
0条评论
avatar