SQL Server 2008 MERGE 進階應用：UPDATE/DELETE、派生來源表、OUTPUT 導入稽核表

案例 A：單一陳述式執行 UPDATE 與 DELETE (庫存扣減)

USE AdventureWorks; GO
IF OBJECT_ID('Production.usp_UpdateInventory','P') IS NOT NULL DROP PROCEDURE Production.usp_UpdateInventory; GO
CREATE PROCEDURE Production.usp_UpdateInventory @OrderDate datetime
AS
MERGE Production.ProductInventory AS target
USING (
    SELECT ProductID, SUM(OrderQty)
    FROM Sales.SalesOrderDetail AS sod
    JOIN Sales.SalesOrderHeader AS soh ON sod.SalesOrderID = soh.SalesOrderID
    WHERE soh.OrderDate = @OrderDate
    GROUP BY ProductID
) AS source (ProductID, OrderQty)
ON (target.ProductID = source.ProductID)
WHEN MATCHED AND target.Quantity - source.OrderQty <= 0 THEN DELETE
WHEN MATCHED THEN UPDATE SET
    target.Quantity = target.Quantity - source.OrderQty,
    target.ModifiedDate = GETDATE()
OUTPUT $action, Inserted.ProductID, Inserted.Quantity, Inserted.ModifiedDate,
       Deleted.ProductID, Deleted.Quantity, Deleted.ModifiedDate; GO

EXECUTE Production.usp_UpdateInventory '20030501';

案例 B：派生來源表 (VALUES) 執行 UPDATE/INSERT (維護銷售原因)

USE AdventureWorks; GO
MERGE INTO Sales.SalesReason AS Target
USING (VALUES ('Recommendation','Other'), ('Review','Marketing'), ('Internet','Promotion'))
    AS Source (NewName, NewReasonType)
ON Target.Name = Source.NewName
WHEN MATCHED THEN UPDATE SET ReasonType = Source.NewReasonType
WHEN NOT MATCHED BY TARGET THEN INSERT (Name, ReasonType) VALUES (NewName, NewReasonType)
OUTPUT $action, inserted.*, deleted.*;

案例 C：將 MERGE 執行結果導入稽核表 (追蹤庫存變動)

USE AdventureWorks; GO
CREATE TABLE Production.UpdatedInventory (
    ProductID INT NOT NULL, LocationID int, NewQty int, PreviousQty int,
    CONSTRAINT PK_Inventory PRIMARY KEY CLUSTERED (ProductID, LocationID)
); GO

INSERT INTO Production.UpdatedInventory
SELECT ProductID, LocationID, NewQty, PreviousQty
FROM (
    MERGE Production.ProductInventory AS pi
    USING (
        SELECT ProductID, SUM(OrderQty)
        FROM Sales.SalesOrderDetail AS sod
        JOIN Sales.SalesOrderHeader AS soh ON sod.SalesOrderID = soh.SalesOrderID
        WHERE soh.OrderDate BETWEEN '20030701' AND '20030731'
        GROUP BY ProductID
    ) AS src (ProductID, OrderQty)
    ON pi.ProductID = src.ProductID
    WHEN MATCHED AND pi.Quantity - src.OrderQty >= 0 THEN UPDATE SET pi.Quantity = pi.Quantity - src.OrderQty
    WHEN MATCHED AND pi.Quantity - src.OrderQty <= 0 THEN DELETE
    OUTPUT $action, Inserted.ProductID, Inserted.LocationID, Inserted.Quantity AS NewQty, Deleted.Quantity AS PreviousQty
) AS Changes (Action, ProductID, LocationID, NewQty, PreviousQty)
WHERE Action = 'UPDATE'; GO

關鍵點總結

1. USING 子句支援子查詢、VALUES、資料表、檢視表等任何表值來源。
2. WHEN MATCHED 可加 AND 進一步篩選 (如庫存不足才刪除)。
3. OUTPUT 子句搭配 $action、inserted、deleted 可將異動明細導入稽核表或變數表。
4. MERGE 為**單一原子陳述式**，要么全成功要么全回滾，保證資料一致性。