BigQuery中如何使用MERGE将匹配记录存入另一表？

错误原因 #

BigQuery的MERGE语法有明确限制：WHEN MATCHED/WHEN NOT MATCHED分支仅允许对MERGE的目标表（此处为tbl_main）执行INSERT/UPDATE/DELETE操作，无法直接在分支中插入到其他表（如tbl_dupes），这是触发语法错误的核心原因。

解决方法 #

方法1：拆分两步操作 #

先将重复记录插入到tbl_dupes，再将新记录合并到tbl_main：

1. 插入重复记录到Dupes表

INSERT INTO tbl_dupes
SELECT temp.*
FROM tbl_temp temp
INNER JOIN tbl_main main 
  ON main.hash = temp.hash

2. 合并新记录到Main表

MERGE tbl_main AS main
USING tbl_temp AS temp
ON main.hash = temp.hash
WHEN NOT MATCHED THEN
  INSERT ROW

方法2：事务包裹实现原子性 #

如果需要确保两个操作的原子性（要么全部成功，要么全部回滚），可以用事务包裹：

BEGIN TRANSACTION;

-- 插入重复记录到Dupes
INSERT INTO tbl_dupes
SELECT temp.*
FROM tbl_temp temp
INNER JOIN tbl_main main 
  ON main.hash = temp.hash;

-- 合并新记录到Main
MERGE tbl_main AS main
USING tbl_temp AS temp
ON main.hash = temp.hash
WHEN NOT MATCHED THEN
  INSERT ROW;

COMMIT TRANSACTION;

内容的提问来源于stack exchange，提问作者hSin