傳統 ETL/同步邏輯需寫三段代碼：
-- 1. 更新現有 UPDATE Target SET ... FROM Target JOIN Source ON ...; -- 2. 插入新增 INSERT Target (...) SELECT ... FROM Source WHERE NOT EXISTS (SELECT 1 FROM Target WHERE ...); -- 3. 刪除目標多餘 (可選) DELETE Target WHERE NOT EXISTS (SELECT 1 FROM Source WHERE ...);
缺點：多次掃描、事務一致性難控制、代碼冗長、並發下易死鎖。
MERGE (SQL Server 2008+)：**單一陳述式、單次掃描、原子性、標準 SQL:2003**。
MERGE INTO TargetTable AS T -- 目標表 (別名 T)
USING SourceTable AS S -- 來源表/子查詢/CTE (別名 S)
ON T.KeyCol = S.KeyCol -- 匹配條件 (ON 子句)
WHEN MATCHED THEN -- 來源與目標均存在
UPDATE SET T.Col1 = S.Col1, ... -- 更新動作
WHEN NOT MATCHED BY TARGET THEN -- 來源有、目標無
INSERT (Col1, Col2...) VALUES (S.Col1, S.Col2...) -- 插入動作
WHEN NOT MATCHED BY SOURCE THEN -- 目標有、來源無 (全量同步才需)
DELETE; -- 刪除動作
-- 可選：OUTPUT $action, inserted.*, deleted.*; -- 記錄異動明細
;
MERGE ... ; 缺少分號報語法錯誤。The MERGE statement attempted to UPDATE or DELETE the same row more than once.UPDATE 一個 DELETE，且第一個需帶 AND <condition> (如 WHEN MATCHED AND T.Col <> S.Col THEN UPDATE)。INSERT；BY SOURCE 只能 UPDATE/DELETE。AFTER INSERT/UPDATE/DELETE 觸發器**依序各觸發一次** (非每行)。場景：每日從暫存表 Stg_Customer 同步至正式表 Dim_Customer。
MERGE INTO Dim_Customer AS T
USING Stg_Customer AS S
ON T.CustomerID = S.CustomerID
WHEN MATCHED AND (T.Name <> S.Name OR T.Email <> S.Email OR T.Phone <> S.Phone) THEN
UPDATE SET T.Name = S.Name, T.Email = S.Email, T.Phone = S.Phone, T.ModifiedDate = GETDATE()
WHEN NOT MATCHED BY TARGET THEN
INSERT (CustomerID, Name, Email, Phone, CreatedDate)
VALUES (S.CustomerID, S.Name, S.Email, S.Phone, GETDATE())
-- 可選：輸出異動日誌
OUTPUT $action AS ActionType,
COALESCE(inserted.CustomerID, deleted.CustomerID) AS CustomerID,
inserted.Name AS NewName, deleted.Name AS OldName
INTO dbo.CustomerSyncLog (ActionType, CustomerID, NewName, OldName, SyncTime)
VALUES ($action, COALESCE(inserted.CustomerID, deleted.CustomerID), inserted.Name, deleted.Name, GETDATE());
亮點：WHEN MATCHED AND ... 只有實際變更才 UPDATE，減少寫入/觸發器/日誌開銷；OUTPUT 子句完整記錄異動審計追蹤。
場景：參數表/字典表定期全量覆蓋，來源為權威主檔。
MERGE INTO dbo.Config AS T
USING (SELECT KeyName, KeyValue, Description FROM MasterDB.dbo.Config) AS S
ON T.KeyName = S.KeyName
WHEN MATCHED AND T.KeyValue <> S.KeyValue THEN
UPDATE SET T.KeyValue = S.KeyValue, T.Description = S.Description
WHEN NOT MATCHED BY TARGET THEN
INSERT (KeyName, KeyValue, Description) VALUES (S.KeyName, S.KeyValue, S.Description)
WHEN NOT MATCHED BY SOURCE THEN
DELETE; -- 目標有但來源無 = 已廢棄配置，刪除
OUTPUT $action, inserted.KeyName, deleted.KeyName INTO dbo.ConfigChangeLog;
-- 來源可能有重複 CustomerID，取最新 (ModifiedDate 最大) 一行
WITH DedupedSource AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY ModifiedDate DESC) AS rn
FROM Stg_Customer
)
MERGE INTO Dim_Customer AS T
USING (SELECT CustomerID, Name, Email, Phone FROM DedupedSource WHERE rn = 1) AS S
ON T.CustomerID = S.CustomerID
WHEN MATCHED THEN UPDATE SET ...
WHEN NOT MATCHED THEN INSERT ...;
WHEN MATCHED AND T.Col <> S.Col 只更新真正變更的行；考慮 READPAST / LOCK HINTS (謹慎)。MAXDOP 設定合理，統計資訊最新。| 錯誤訊息 | 原因 | 解決 |
|---|---|---|
The MERGE statement attempted to UPDATE or DELETE the same row more than once. | 來源 ON 欄位有重複值 | 來源預先去重 (ROW_NUMBER/CTE/DISTINCT) |
Incorrect syntax near 'MERGE'. | 缺少分號結尾 或 版本 < 2008 | 加分號；升級或改用分離陳述式 |
The target table 'X' of the MERGE statement cannot have any enabled triggers if the MERGE statement contains an OUTPUT clause without INTO clause. | 有 OUTPUT 無 INTO 且目標表有觸發器 | 加上 OUTPUT ... INTO LogTable 或移除 OUTPUT |
| 死鎖 | 並發 MERGE 同一目標表，鎖升級 | 分批、加索引、調整隔離級別、應用層串行化 |
https://technet.microsoft.com/zh-cn/library/bb510625.aspx
MERGE 是 SQL Server **資料同步、UPSERT、緩慢變維度 (SCD) 處理**的首選武器。核心要訣：**來源去重保證唯一性、MATCHED 條件精準過濾變更行、OUTPUT 子句落地審計日誌、分批處理大數據量**。掌握本文三大實戰範例與避坑指南，即可在生產環境安全部署高效、可靠的 MERGE 同步邏輯。
