MERGE 結合 INSERT、UPDATE、DELETE 於單一陳述式,依據來源與目標表的連接條件,一次掃描即完成同步,效能優於分開執行。
-- 建立測試表
CREATE TABLE a (id_ INTEGER, count_ INTEGER);
CREATE TABLE b (id_ INTEGER, count_ INTEGER);
INSERT INTO a VALUES (1,3); INSERT INTO a VALUES (3,6);
INSERT INTO b VALUES (1,7); INSERT INTO b VALUES (2,4);
-- MERGE 同步:b 合併進 a
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 表變為 (1,10), (3,6), (2,4)
SELECT * FROM a ORDER BY id_;
-- 建立測試表
CREATE TABLE test1 (id INT PRIMARY KEY, name VARCHAR(20));
CREATE TABLE test2 (id INT PRIMARY KEY, name VARCHAR(20));
INSERT INTO test1 VALUES (1,'boyi55'),(2,'51cto'),(3,'bbs'),(4,'fengjicai'),(5,'alis');
INSERT INTO test2 VALUES (1,'boyi'),(2,'51cto');
-- 將 test1 同步至 test2
MERGE INTO test2 AS t
USING test1 AS s
ON t.id = s.id
WHEN MATCHED THEN
UPDATE SET t.name = s.name
WHEN NOT MATCHED BY TARGET THEN
INSERT (id, name) VALUES (s.id, s.name)
WHEN NOT MATCHED BY SOURCE THEN
DELETE; -- 選項:來源沒有的目標資料刪除
;
-- 驗證結果
SELECT a.id, a.name AS src_name, b.name AS tgt_name
FROM test1 a JOIN test2 b ON a.id = b.id;
; 結束(SQL Server 強制)。WHEN NOT MATCHED BY SOURCE 可處理目標表有但來源無的資料(刪除或標記)。INSERT/UPDATE/DELETE 分別各一次。