Search code examples
sqlgoogle-bigquerymerge

MERGE WHEN MATCHED into different table

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
Solution

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