萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MS SQL 資料庫

SQL Server MERGE 語法完整指南:一條語句搞定 INSERT/UPDATE/DELETE

SQL Server MERGE 語法完整指南:一條語句搞定 INSERT/UPDATE/DELETE

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。

另一範例:UserInfo 與 ChartInfo 同步

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');

限制與最佳實務

  1. 同一 MATCHED 只能出現一次 UPDATE/DELETE;需分兩個子句時,第一個必須加 AND <condition>。
  2. NOT MATCHED 只能 INSERT;BY SOURCE 可 UPDATE/DELETE。
  3. 不可重複更新同一行:來源資料鍵值必須唯一,否則報錯「The MERGE statement attempted to UPDATE or DELETE the same row more than once」。
  4. 效能考量:大量資料建議加上 OPTION (MAXDOP 1) 或分批處理,避免鎖升級與 TempDB 壓力。
  5. 觸發器:MERGE 會依實際動作觸發對應的 AFTER INSERT/UPDATE/DELETE Trigger。
  6. OUTPUT 子句:搭配 $action 可記錄每行實際執行的操作(INSERT/UPDATE/DELETE),利於稽核。

適用場景

  • 資料倉庫維度表 SCD Type 1/2 載入。
  • 主從表同步(如 BI 系統子表變動同步至父表)。
  • 離線資料定期合併至線上表。
  • 避免「先 SELECT 再判斷 INSERT/UPDATE」的往返與競態條件。

小結

MERGE 將 DML 操作集中化、原子化,減少網路往返與程式複雜度。掌握三大 WHEN 子句邏輯、來源鍵值唯一性、OUTPUT 稽核,即可在 ETL 與同步場景中發揮最大效能。

copyright © 萬盛學電腦網 all rights reserved