title: Multiple OUTPUT clauses in MERGE/INSERT/DELETE SQL commands?
body: <p>I have a <code>T-SQL</code> script that implements some synchronization logic using <code>OUTPUT</code> clause in <code>MERGE</code>s and <code>INSERT</code>s.</p>
  
  <p>Now I am adding a logging layer over it and I would like to add a second <code>OUTPUT</code> clause to write the values into a report table.</p>
  
  <p>I can add a second <code>OUTPUT</code> clause to my <code>MERGE</code> statement:</p>
  
  <pre><code>MERGE TABLE_TARGET AS T
  USING TABLE_SOURCE AS S
  ON (T.Code = S.Code) 
  WHEN MATCHED AND T.IsDeleted = 0x0
      THEN UPDATE SET ....
  WHEN NOT MATCHED BY TARGET 
      THEN INSERT ....
  OUTPUT inserted.SqlId, inserted.IncId
  INTO @sync_table
  OUTPUT $action, inserted.Name, inserted.Code;
  </code></pre>
  
  <p>And this works, but as long as I try to add the target</p>
  
  <pre><code>INTO @report_table;
  </code></pre>
  
  <p>I get the following error message before <code>INTO</code>:</p>
  
  <pre><code>A MERGE statement must be terminated by a semicolon (;)
  </code></pre>
  
  <p>I found <a href="https://stackoverflow.com/questions/13094099/sql-server-multiple-output-clauses">a similar question here</a>, but it didn't help me further, because the fields I am going to insert do not overlap between two tables and I don't want to modify the working sync logic (if possible).</p>
  
  <p><strong>UPDATE:</strong></p>
  
  <p>After the answer by <a href="https://stackoverflow.com/users/73226/martin-smith">Martin Smith</a> I had another idea and re-wrote my query as following:</p>
  
  <pre><code>INSERT INTO @report_table (action, name, code)
  SELECT M.Action, M.Name, M.Code
  FROM
  (
  MERGE TABLE_TARGET AS T
  USING TABLE_SOURCE AS S
  ON (T.Code = S.Code) 
  WHEN MATCHED AND T.IsDeleted = 0x0
      THEN UPDATE SET ....
  WHEN NOT MATCHED BY TARGET 
      THEN INSERT ....
  OUTPUT inserted.SqlId, inserted.IncId
  INTO @sync_table
  OUTPUT $action as Action, inserted.Name, inserted.Code
  ) M
  </code></pre>
  
  <p>Unfortunately this approach did not work either, the following error message is output at runtime:</p>
  
  <pre><code>An OUTPUT INTO clause is not allowed in a nested INSERT, UPDATE, DELETE, or MERGE statement.
  </code></pre>
  
  <p>So, there is definitely no way to have multiple <code>OUTPUT</code> clauses in a single DML statement.</p>