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

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

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

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

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

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 ;