sqlserver merge 操作符

 

语法：

MERGE

　　[ INTO ] <target_table> [ [ AS ] table_alias ]

　　USING <table_source> [ [ AS ] table_alias ]

　　ON <merge_search_condition>

[ WHEN MATCHED [ AND <clause_search_condition> ]

　　THEN <merge_matched> ] [ ...n ]

[ WHEN NOT MATCHED [ BY TARGET ] [ AND <clause_search_condition> ]

　　THEN <merge_not_matched> ]

[ WHEN NOT MATCHED BY SOURCE [ AND <clause_search_condition> ]

　　THEN <merge_matched> ] [ ...n ]

 

先建立一张新表

CREATE TABLE Sales.LastCustomerOrder
(
　　CustomerID INT,
　　SalesOrderID INT,
　　CONSTRAINT pk_LastCustomerOrder PRIMARY KEY CLUSTERED (CustomerId)
) ;

 

下面检查一行数据，由于目标表没有改记录，那么直接插入改记录

DECLARE @CustomerID INT = 100,
        @SalesOrderID INT = 101 ;

MERGE INTO Sales.LastCustomerOrder AS tgt
    USING
        (SELECT @CustomerID AS CustomerID,
                @SalesOrderID AS SalesOrderID
        ) AS src
    ON tgt.CustomerID = src.CustomerID
    WHEN MATCHED
        THEN UPDATE
            SET SalesOrderID = src.SalesOrderID
    WHEN NOT MATCHED
        THEN INSERT (
            CustomerID,
            SalesOrderID
            )
        VALUES (src.CustomerID,
                src.SalesOrderID) ;

 

结果

 

 

再将下面测试数据带入merge 语句

 

 然后结果

 

---

 

再建立一张表，多加一个字段 money ,用于存储订单最大钱数，如果后续来自 source 订单的钱数大于当前订单，更新数据，否则不更新，当然没有的数据要添加

CREATE TABLE Sales.LargestCustomerOrder
(
    CustomerID INT,
    SalesOrderID INT,
    TotalDue MONEY,
    CONSTRAINT pk_LargestCustomerOrder PRIMARY KEY CLUSTERED (CustomerId)
);

 

merge 语句

DECLARE @CustomerID INT = 100,
        @SalesOrderID INT = 101 ,
        @TotalDue MONEY = 1000.00;
MERGE INTO Sales.LargestCustomerOrder AS tgt
USING
    (SELECT @CustomerID AS CustomerID,
    @SalesOrderID AS SalesOrderID,
    @TotalDue AS TotalDue
    ) AS src
ON tgt.CustomerID = src.CustomerID
    WHEN MATCHED AND tgt.TotalDue < src.TotalDue
        THEN UPDATE
            SET SalesOrderID = src.SalesOrderID
            , TotalDue = src.TotalDue
    WHEN NOT MATCHED
        THEN INSERT (
            CustomerID,
            SalesOrderID,
            TotalDue
            )
            VALUES (src.CustomerID,
            src.SalesOrderID,
            src.TotalDue);

 

选择 matched 的顺序很重要，下面的情况会执行第一个为true 的分支

WHEN MATCHED AND tgt.TotalDue < src.TotalDue
　　　　THEN UPDATE
　　　　　　SET SalesOrderID = src.SalesOrderID
　　　　　　, TotalDue = src.TotalDue
WHEN MATCHED
　　　　THEN UPDATE
　　　　SET SalesOrderID = src.SalesOrderID

 

---

 

针对每一条记录操作的详细信息，下面的语句将会显示 执行的操作的统计信息

DECLARE @CustomerID INT = 100,
        @SalesOrderID INT = 201 ,
        @TotalDue MONEY = 1200.00;
MERGE INTO Sales.LargestCustomerOrder AS tgt
USING
        (SELECT @CustomerID AS CustomerID,
        @SalesOrderID AS SalesOrderID,
        @TotalDue AS TotalDue
        ) AS src
ON tgt.CustomerID = src.CustomerID
    WHEN MATCHED AND tgt.TotalDue < src.TotalDue
        THEN UPDATE
            SET SalesOrderID = src.SalesOrderID
            , TotalDue = src.TotalDue
    WHEN NOT MATCHED
        THEN INSERT (
        CustomerID,
        SalesOrderID,
        TotalDue
        )
        VALUES (src.CustomerID,
        src.SalesOrderID,
        src.TotalDue)

    OUTPUT
    $ACTION,
    DELETED.*,
    INSERTED.*;

结果