In BigQuery how do you use MERGE to have the matched records go into a different table? I have 3 tables: Main, Temp, and Dupes. Temp receives data from a Python script on a schedule. Main is where new records are inserted compared to records in Temp. And Dupes is where I want the duplicate records to go. It looks like INSERT INTO doesn't work with MERGE.
MERGE tbl_main AS main
USING tbl_temp as temp
ON main.hash = temp.hash
WHEN NOT MATCHED THEN
INSERT ROW
WHEN MATCHED THEN
INSERT INTO tbl_dupes SELECT * FROM temp --fails here
error:
400 Syntax error: Expected keyword ROW or keyword VALUES but got keyword INTO at [11:14]; reason: invalidQuery, location: query, message: Syntax
error: Expected keyword ROW or keyword VALUES but got keyword INTO at
MERGE's standard behaviour - based on the ANSI standard - is to have one target and one source.
You'll have to split this process into two statements:
One:
INSERT INTO tbl_main
SELECT tmp.*
FROM tbl_temp tmp
LEFT JOIN tbl_main main ON main.hash = tmp.hash
WHERE main.hash IS NULL;
-- or, alternatively, a WHERE NOT EXISTS predicate instead of the left join ...
Two:
INSERT INTO tbl_dupes
SELECT tmp.*
FROM tbl_temp tmp
JOIN tbl_main main ON main.hash=tmp.hash;