title: Logging MERGE Changes in BigQuery
body: <p>I am looking for a way to replicate the functionality of SQL Server's OUTPUT clause when using BigQuery's MERGE statement (assuming BQ does not have it already in some form and I missed it). The goal is to produce a table containing a count of the rows updated, deleted, and inserted by the BQ MERGE. A simple example, the following SQL statements:</p>
  <pre><code>CREATE OR REPLACE TABLE `scratch.Events` AS 
   (SELECT 'ABC' as Id, '123' as Sku, 'Orange' as Name UNION ALL
    SELECT 'DEF', '456', 'Banana' UNION ALL
    SELECT 'GHI', '789', 'Honeycrisp' UNION ALL
    SELECT 'JKL', '012', 'Pear' UNION ALL
    SELECT 'MNO', '345', 'Strawberry');
  
  CREATE OR REPLACE TABLE `scratch.NewEvents` AS
   (SELECT 'DEF' as Id, '456' as Sku, 'Banana' as Name UNION ALL
    SELECT 'GHI', '789', 'Apple' UNION ALL
    SELECT 'PQR', '456', 'Banana');
  
  MERGE scratch.Events e 
  USING scratch.NewEvents ne
  ON e.id = ne.id AND e.sku = ne.sku
  WHEN MATCHED AND e.name != ne.name THEN
    UPDATE SET name = ne.name
  WHEN NOT MATCHED THEN
    INSERT ROW
  WHEN NOT MATCHED BY SOURCE THEN
    DELETE;
  </code></pre>
  <p>Produce:</p>
  <p><a href="https://i.stack.imgur.com/typSi.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/typSi.jpg" alt="enter image description here" /></a></p>
  <p>SQL Server's OUTPUT clause could be used to produce the following:</p>
  <p><a href="https://i.stack.imgur.com/7ScXh.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/7ScXh.jpg" alt="enter image description here" /></a></p>
  <p>Any ideas on how to efficiently get similar output in BigQuery would be greatly appreciated!</p>