SQL Server 2008 MERGE 語法實戰：UPSERT、同步、審計輸出三大經典場景

MERGE 核心語法

MERGE INTO  AS Target
USING  AS Source
ON 
WHEN MATCHED THEN 
WHEN NOT MATCHED BY TARGET THEN 
WHEN NOT MATCHED BY SOURCE THEN 
OUTPUT $action, inserted.*, deleted.*;

場景一：單一陳述式 UPSERT (更新/插入)

每日依銷售明細扣減庫存，庫存歸零則刪除產品列。

CREATE PROCEDURE Production.usp_UpdateInventory @OrderDate DATETIME
AS
MERGE Production.ProductInventory AS Target
USING (
    SELECT ProductID, SUM(OrderQty) AS TotalQty
    FROM Sales.SalesOrderDetail sod
    JOIN Sales.SalesOrderHeader 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;

場景二：參考表同步 (更新/插入)，來源為衍生表/值建構器

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 (Source.NewName, Source.NewReasonType)
OUTPUT $action, inserted.*, deleted.*;

VALUES 列建構器 (SQL 2008+) 替代 UNION ALL，簡潔高效。

場景三：MERGE 結果寫入審計表

CREATE TABLE Production.UpdatedInventory (
    ProductID INT NOT NULL,
    LocationID INT,
    NewQty INT,
    PreviousQty INT,
    CONSTRAINT PK_UpdatedInventory PRIMARY KEY CLUSTERED (ProductID, LocationID)
)

INSERT INTO Production.UpdatedInventory (ProductID, LocationID, NewQty, PreviousQty)
SELECT ProductID, LocationID, NewQty, PreviousQty
FROM (
    MERGE Production.ProductInventory AS Target
    USING (
        SELECT ProductID, SUM(OrderQty) AS OrderQty
        FROM Sales.SalesOrderDetail sod
        JOIN Sales.SalesOrderHeader soh ON sod.SalesOrderID = soh.SalesOrderID
        WHERE soh.OrderDate BETWEEN '20030701' AND '20030731'
        GROUP BY ProductID
    ) AS Source (ProductID, OrderQty)
    ON Target.ProductID = Source.ProductID
    WHEN MATCHED AND Target.Quantity - Source.OrderQty >= 0
        THEN UPDATE SET Target.Quantity = Target.Quantity - Source.OrderQty
    WHEN MATCHED AND Target.Quantity - Source.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';

關鍵優勢

效能與最佳實踐

1. 目標表有索引：ON 條件欄位建立索引 (通常是 PK)
2. 來源資料預聚合：USING 子查詢先 GROUP BY，減少比對列
3. 避免大量列更新：僅更新變更欄位，UPDATE SET Col = Source.Col WHERE Target.Col <> Source.Col
4. OUTPUT 導向審計表：避免觸發器開銷
5. HOLDLOCK 提示：MERGE ... WITH (HOLDLOCK) 序列化存取，防止幻讀
6. TOP 限制：分批處理大量同步，避免長交易鎖升級

常見陷阱

• WHEN NOT MATCHED BY SOURCE DELETE：若來源未包含全量資料會誤刪 → 僅用於全量同步
• 多重 MATCHED：SQL Server 僅支援單一 WHEN MATCHED，條件用 CASE/IIF 在 SET 中處理
• 遞迴觸發：MERGE 觸發 UPDATE 觸發器再 MERGE → 避免或用 TRIGGER_NESTLEVEL 判斷
• NULL 比較：ON 條件中 NULL 不匹配 → 用 IS NULL 或 ISNULL() 處理

適用版本與替代方案

• SQL Server 2008+ 完整支援
• Azure SQL Database / Managed Instance 完整支援
• 舊版替代：INSERT ... ON DUPLICATE KEY UPDATE (MySQL)、UPSERT (PostgreSQL 9.5+)、應用層邏輯