您现在的位置：程式碼之家 >> 數據庫知識 > >> SQL Server 資料庫 >> 關於SqlServer

SQL MERGE 語法全解析：Oracle / SQL Server 雙平台 UPSERT 實戰與效能優勢

編輯：關於SqlServer

MERGE 核心價值

**單一陳述式完成「存在即更新、不存在即新增」(UPSERT)**。
**單次掃描來源/目標**，避免分開 `SELECT` → `IF EXISTS` → `UPDATE/INSERT` 多次往返與競態條件。
支援平台：Oracle 9i+、SQL Server 2008+、**不支援** MySQL (用 `INSERT ... ON DUPLICATE KEY UPDATE`)、PostgreSQL (用 `INSERT ... ON CONFLICT`)。

Oracle MERGE 範例

-- 目標表 A，來源表 B
MERGE INTO a
USING b
ON (a.id_ = b.id_)
WHEN MATCHED THEN
    UPDATE SET a.count_ = a.count_ + b.count_  -- 明確指定表前綴
WHEN NOT MATCHED THEN
    INSERT (id_, count_) VALUES (b.id_, b.count_);

測試資料與結果

-- A 表
ID_  COUNT_
1    3
3    6

-- B 表
ID_  COUNT_
1    7
2    4

-- MERGE 後 A 表
ID_  COUNT_
1    10   (3+7)
3    6
2    4   (新增)

SQL Server MERGE 範例

-- 目標表 test2，來源表 test1
MERGE test2 AS t          -- 目標表別名
USING test1 AS s          -- 來源表別名
ON t.id = s.id            -- 連接條件 (通常為主鍵/唯一鍵)
WHEN MATCHED THEN
    UPDATE SET t.name = s.name
WHEN NOT MATCHED THEN
    INSERT (id, name) VALUES (s.id, s.name);  -- 必須以分號結束

測試資料與結果

-- test1 (來源)
ID  NAME
1   boyi55
2   51cto
3   bbs
4   fengjicai
5   alis

-- test2 (目標，初始)
ID  NAME
1   boyi
2   51cto

-- MERGE 後 test2
ID  NAME
1   boyi55   (更新)
2   51cto    (無變化)
3   bbs      (新增)
4   fengjicai (新增)
5   alis     (新增)

進階用法：條件式更新/刪除、OUTPUT 子句

SQL Server：WHEN MATCHED AND 條件

MERGE target AS t
USING source AS s ON t.id = s.id
WHEN MATCHED AND s.status = 'ACTIVE' THEN
    UPDATE SET t.val = s.val, t.modified = GETDATE()
WHEN MATCHED AND s.status = 'DELETED' THEN
    DELETE
WHEN NOT MATCHED THEN
    INSERT (id, val, status) VALUES (s.id, s.val, s.status);

OUTPUT 子句：記錄實際執行動作

MERGE target AS t
USING source AS s ON t.id = s.id
WHEN MATCHED THEN UPDATE SET t.val = s.val
WHEN NOT MATCHED THEN INSERT (id, val) VALUES (s.id, s.val)
OUTPUT $action AS Action, inserted.id AS NewID, deleted.id AS OldID, inserted.val AS NewVal;

`$action` 回傳 `'INSERT'` / `'UPDATE'` / `'DELETE'`。
可 `INTO @TableVar` 暫存審計日誌。

效能與最佳實踐

原則	說明
目標表連接欄位需唯一索引/主鍵	確保 `ON` 條件精準匹配單行，避免多行匹配導致錯誤 `The MERGE statement attempted to UPDATE or DELETE the same row more than once.`
來源資料預先去重	來源若含重複鍵，會觸發上述錯誤。用 `ROW_NUMBER() OVER (PARTITION BY key ORDER BY ...)` 取首行。
大批量 UPSERT 分批	單次 MERGE 處理 >10 萬行易導致長鎖、記錄檔暴漲。分批 (如 5k/批) 循環執行。
鎖提示謹慎使用	`WITH (HOLDLOCK)` 可防止 Phantom Read 但增加死鎖風險。評估隔離級別 (RCSI) 替代。
觸發器順序	MERGE 觸發 `INSERT`、`UPDATE` 觸發器 (若定義)，順序：`INSTEAD OF` → `AFTER`。

對比：MERGE vs 手工 UPSERT

-- 手工模式 (兩次往返、有競態)
IF EXISTS (SELECT 1 FROM target WHERE id = @id)
    UPDATE target SET val = @val WHERE id = @id;
ELSE
    INSERT target (id, val) VALUES (@id, @val);