MERGE 於 SQL Server 2008 引入,可根據來源與目標表的比對結果,在單一陳述式中完成插入、更新、刪除,極大簡化 ETL、資料同步、緩慢變維度(SCD)邏輯。本文詳解語法結構、關鍵子句、實戰範例與限制事項。
MERGE INTO TargetTable AS T USING SourceTable AS S ON T.KeyCol = S.KeyCol WHEN MATCHED THEN UPDATE SET T.Col1 = S.Col1, ... WHEN NOT MATCHED BY TARGET THEN INSERT (KeyCol, Col1, ...) VALUES (S.KeyCol, S.Col1, ...) WHEN NOT MATCHED BY SOURCE THEN DELETE OUTPUT $action, INSERTED.*, DELETED.*; -- 可選,記錄操作類型與變更
| 子句 | 觸發條件 | 允許操作 |
|---|---|---|
| WHEN MATCHED | 目標與來源鍵值相符 | UPDATE / DELETE(僅能二選一,或用 AND 區分兩個 MATCHED 子句:一個 UPDATE、一個 DELETE) |
| WHEN NOT MATCHED [BY TARGET] | 來源有、目標無 | INSERT |
| WHEN NOT MATCHED BY SOURCE | 目標有、來源無 | UPDATE / DELETE |
注意:陳述式必須以分號 ; 結尾。
MERGE INTO icr_codemap_bak AS A USING icr_codemap AS B ON A.COLNAME = B.COLNAME AND A.ctcode = B.ctcode WHEN MATCHED AND B.pbcode <> A.pbcode THEN UPDATE SET A.pbcode = B.pbcode WHEN NOT MATCHED BY TARGET THEN INSERT (COLNAME, ctcode, pbcode, note) VALUES (B.COLNAME, B.ctcode, B.pbcode, B.note);
效果:
① 鍵值相同但 pbcode 不同 → 更新。
② 來源有、目標無 → 新增。
③ 目標有、來源無 → 本例未處理(保留歷史),若需刪除可加 WHEN NOT MATCHED BY SOURCE THEN DELETE。
MERGE INTO UserInfo AS U
USING ChartInfo AS C
ON U.UserId = C.UserId
WHEN MATCHED AND U.UserName = C.UserName
THEN UPDATE SET U.LastUpdate = C.LastUpdate
WHEN NOT MATCHED BY TARGET
THEN INSERT (UserName, Sex) VALUES ('ZhangZhao', 'M');
AND <condition>。OPTION (MAXDOP 1) 或分批處理,避免鎖升級與 TempDB 壓力。$action 可記錄每行實際執行的操作(INSERT/UPDATE/DELETE),利於稽核。MERGE 將 DML 操作集中化、原子化,減少網路往返與程式複雜度。掌握三大 WHEN 子句邏輯、來源鍵值唯一性、OUTPUT 稽核,即可在 ETL 與同步場景中發揮最大效能。