SQL Server 2008 MERGE 陳述式完整指南：單一陳述式執行 INSERT、UPDATE、DELETE

前言

MERGE 是 SQL Server 2008 新增的強力 DML 陳述式，能依據來源資料與目標資料表的聯結結果，在**單一陳述式**中完成插入、更新、刪除，極適合資料同步、ETL 上傳等情境。

基本語法結構

MERGE INTO <目標表> AS Target
USING <來源資料> AS Source
ON <聯結條件>
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];

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

-- 建立目標表：總產品
IF OBJECT_ID('Demo_AllProducts') IS NOT NULL DROP TABLE Demo_AllProducts; GO
CREATE TABLE Demo_AllProducts (
    PKID int IDENTITY(1,1) PRIMARY KEY,
    DName Nvarchar(20), DCode NVarchar(30), DDate datetime
); GO
INSERT Demo_AllProducts (DName,DCode,DDate) VALUES
('DemoA','AAA',GETDATE()),('DemoB','BBB',GETDATE()),('DemoC','CCC',GETDATE()),
('DemoD','DDD',GETDATE()),('DemoE','EEE',GETDATE()); GO

-- 建立來源表：分店產品
IF OBJECT_ID('Demo_Shop1_Product') IS NOT NULL DROP TABLE Demo_Shop1_Product; GO
CREATE TABLE Demo_Shop1_Product (
    PKID int IDENTITY(1,1) PRIMARY KEY,
    DName Nvarchar(20), DCode NVarchar(30), DDate datetime
); GO
INSERT Demo_Shop1_Product (DName,DCode,DDate) VALUES
('DemoA','AAA',GETDATE()),('DemoB','CCC',GETDATE()),('DemoF','FFF',GETDATE()); GO

情境 A：完整同步 (含刪除目標表多餘資料)

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; GO

-- 結果：僅保留 AAA, CCC, FFF 三筆 (BBB, DDD, EEE 被刪除)

情境 B：僅新增/更新，不刪除

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

-- 結果：保留原有 AAA, BBB, DDD, EEE，更新 BBB→CCC，新增 FFF

OUTPUT 子句記錄異動明細

DECLARE @tableVarRecord TABLE (
    MPKID int IDENTITY(1,1) PRIMARY KEY,
    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 deleted.* INTO @tableVarRecord;  -- 只記錄刪除的資料

SELECT @@ROWCOUNT AS Count1, ROWCOUNT_BIG() AS Count2;  -- 影響總列數
SELECT * FROM @tableVarRecord;  -- 刪除的資料明細

Count1 Count2
5      5
MPKID PKID DName DCode DDate
1     NULL NULL   NULL  NULL          -- INSERT 無 deleted 資料
2     2    DemoB BBB   ...             -- DELETE 的資料
3     3    DemoC CCC   ...
4     4    DemoD DDD   ...
5     5    DemoE EEE   ...

@@ROWCOUNT 回傳 int (上限 21 億)；ROWCOUNT_BIG() 回傳 bigint，適用超大量異動。

小結

1. MERGE 將多個 DML 整合為單一原子操作，減少鎖定衝突、簡化程式碼。
2. 三大子句：WHEN MATCHED (更新/刪除)、WHEN NOT MATCHED BY TARGET (插入)、WHEN NOT MATCHED BY SOURCE (刪除目標多餘)。
3. OUTPUT 子句可捕獲 $action (INSERT/UPDATE/DELETE)、inserted、deleted 虛擬表，便於稽核或後續處理。