google cloud platform - How to store bigquery merge output into another table

Question

Is there any way to store the output of BigQuery merge in a new data table? If not, is it possible to view the results somewhere in GCP? I don't want to modify the target table.

This is the query I'm using-

MERGE dataset.target T
USING dataset.source S
ON T.c1 = S.c1
WHEN NOT MATCHED BY SOURCE THEN
  DELETE

question from:https://stackoverflow.com/questions/65921940/how-to-store-bigquery-merge-output-into-another-table

Answer 1

If you want to see the rows deleted, you can copy dataset.target into dataset.target2, then execute your MERGE on dataset.target, then see the difference between dataset.target2 and dataset.target:

SELECT * FROM dataset.target2
EXCEPT
SELECT * FROM dataset.target