SQL Server 2008 MERGE 陳述式完整教學：單一指令完成 UPSERT 與同步

前言

MERGE 是 SQL Server 2008 引入的標準 SQL 功能，依據來源與目標表的比對結果，**單一陳述式即可執行 INSERT、UPDATE、DELETE**，極適合資料同步、ETL 維度表維護、緩慢變維度 (SCD) 處理。

語法結構

MERGE INTO 目標表 AS Target
USING 來源表/查詢 AS Source
ON Target.Key = Source.Key
WHEN MATCHED [AND 額外條件] THEN UPDATE SET ...
WHEN NOT MATCHED BY TARGET THEN INSERT (...) VALUES (...)
WHEN NOT MATCHED BY SOURCE THEN DELETE
[OUTPUT $action, inserted.*, deleted.* INTO @TableVar];

完整範例：總產品表與分店產品表同步

1. 建立測試資料

-- 總產品表
CREATE TABLE Demo_AllProducts (
    PKID INT IDENTITY(1,1) PRIMARY KEY,
    DName NVARCHAR(20), DCode NVARCHAR(30), DDate DATETIME
);
INSERT INTO Demo_AllProducts (DName, DCode, DDate) VALUES
(N'DemoA', N'AAA', GETDATE()), (N'DemoB', N'BBB', GETDATE()),
(N'DemoC', N'CCC', GETDATE()), (N'DemoD', N'DDD', GETDATE()),
(N'DemoE', N'EEE', GETDATE());

-- 分店產品表 (來源)
CREATE TABLE Demo_Shop1_Product (
    PKID INT IDENTITY(1,1) PRIMARY KEY,
    DName NVARCHAR(20), DCode NVARCHAR(30), DDate DATETIME
);
INSERT INTO Demo_Shop1_Product (DName, DCode, DDate) VALUES
(N'DemoA', N'AAA', GETDATE()), (N'DemoB', N'CCC', GETDATE()), -- DemoB 編碼異動
(N'DemoF', N'FFF', GETDATE()); -- 新產品

2. 完整同步 (含刪除目標獨有)

MERGE INTO Demo_AllProducts AS P
USING Demo_Shop1_Product AS S
ON P.DCode = S.DCode
WHEN MATCHED AND P.DName <> S.DName
    THEN UPDATE SET P.DName = S.DName
WHEN NOT MATCHED BY TARGET
    THEN INSERT (DName, DCode, DDate) VALUES (S.DName, S.DCode, S.DDate)
WHEN NOT MATCHED BY SOURCE
    THEN DELETE;

結果：目標表僅保留 AAA, CCC, FFF 三筆，DemoB 名稱更新為 CCC，BBB, DDD, EEE 被刪除。

3. 僅合併/更新 (不刪除)

MERGE INTO Demo_AllProducts AS P
USING Demo_Shop1_Product AS S
ON P.DCode = S.DCode
WHEN MATCHED AND P.DName <> S.DName
    THEN UPDATE SET P.DName = S.DName
WHEN NOT MATCHED BY TARGET
    THEN INSERT (DName, DCode, DDate) VALUES (S.DName, S.DCode, S.DDate);
-- 省略 WHEN NOT MATCHED BY SOURCE

結果：目標表保留原有 BBB, DDD, EEE，新增 FFF，更新 DemoB→CCC，共 6 筆。

OUTPUT 子句記錄異動明細

DECLARE @Changes TABLE (
    ActionType NVARCHAR(10), PKID INT, DName NVARCHAR(20), DCode NVARCHAR(30), DDate DATETIME
);

MERGE INTO Demo_AllProducts AS P
USING Demo_Shop1_Product AS S
ON P.DCode = S.DCode
WHEN MATCHED AND P.DName <> S.DName
    THEN UPDATE SET P.DName = S.DName
WHEN NOT MATCHED BY TARGET
    THEN INSERT (DName, DCode, DDate) VALUES (S.DName, S.DCode, S.DDate)
WHEN NOT MATCHED BY SOURCE
    THEN DELETE
OUTPUT $action, inserted.PKID, inserted.DName, inserted.DCode, inserted.DDate,
       deleted.PKID, deleted.DName, deleted.DCode, deleted.DDate
INTO @Changes;

SELECT * FROM @Changes;

$action 回傳 INSERT/UPDATE/DELETE，搭配 inserted/deleted 虛擬表完整記錄異動前後值。

取得影響列數

SELECT @@ROWCOUNT AS TotalRows;          -- 總影響列數
SELECT ROWCOUNT_BIG() AS TotalRowsBig;   -- 大量資料 (>20億) 使用

MERGE 限制與最佳實踐

適用場景

• 資料倉儲維度表每日/每小時同步 (SCD Type 1)。
• 主從資料庫雙向同步衝突解決。
• API 批次 Upsert (客戶端傳入 TVP 當來源)。
• 定期全量覆蓋參考資料表 (郵遞區號、幣別、組織架構)。