title: Best ways to log action by merge statement, potentially with ability to identify error in TSQL
body: <p>I use <a href="http://technet.microsoft.com/en-us/library/bb510625.aspx" rel="nofollow noreferrer">Merge</a> statement to perform table population, I am looking for a best way to implement error logging for the merge statement.</p>
  <p>Initially, I want to log the number of rows that are affected by actions (e.g. insert, update, delete action) performed by merge statement, rather than detailed row contents. However, the logged details might not be enough for identifying cause of error, should error have risen.</p>
  <p>The <strong>second option</strong> is to log all the rows that Merge carried out into a log table. However, the downside is that one logging table is required for each business table; and to keep all the data.</p>
  <p>However, because merge performs atomic operations, which means there is no data to log when error occurred. Thus, the error logging function is probably used for tracking potential bug in the table population logic (please correct it if it is wrong).</p>
  <p>I want a way to log error during the merge statement, so I can identify error, or even recover data.</p>
  <p>Any advice would be very much appreciated.</p>
  <p>To save the result of Merge, use Merge OUTPUT INTO to insert all affected row into a table, and the @@RowCount, below:</p>
  <pre><code>DECLARE @ChangedTable Table (
  Action Varchar(20),
  TargetProductID Int,
  TargetProductName Varchar(100),
  TargetRate Money,
  SourceProductID Int, 
  SourceProductName Varchar(100), 
  SourceRate Money
  )
  
  --Synchronize the target table with
  --refreshed data from source table
  MERGE Products AS TARGET
  USING UpdatedProducts AS SOURCE 
  ON (TARGET.ProductID = SOURCE.ProductID) 
  --When records are matched, update 
  --the records if there is any change
  WHEN MATCHED AND TARGET.ProductName &lt;&gt; SOURCE.ProductName 
  OR TARGET.Rate &lt;&gt; SOURCE.Rate THEN 
  UPDATE SET TARGET.ProductName = SOURCE.ProductName, 
  TARGET.Rate = SOURCE.Rate 
  
  --When no records are matched, insert
  --the incoming records from source
  --table to target table
  WHEN NOT MATCHED BY TARGET THEN 
  INSERT (ProductID, ProductName, Rate) 
  VALUES (SOURCE.ProductID, SOURCE.ProductName, SOURCE.Rate)
  --When there is a row that exists in target table and
  --same record does not exist in source table
  --then delete this record from target table
  WHEN NOT MATCHED BY SOURCE THEN 
  DELETE
  --$action specifies a column of type nvarchar(10) 
  --in the OUTPUT clause that returns one of three 
  --values for each row: 'INSERT', 'UPDATE', or 'DELETE', 
  --according to the action that was performed on that row
  OUTPUT $action, 
  DELETED.ProductID AS TargetProductID, 
  DELETED.ProductName AS TargetProductName, 
  DELETED.Rate AS TargetRate, 
  INSERTED.ProductID AS SourceProductID, 
  INSERTED.ProductName AS SourceProductName, 
  INSERTED.Rate AS SourceRate
  INTO @ChangedTable;
  
  select * from @ChangedTable;
  
  SELECT @@ROWCOUNT;
  </code></pre>
  <p>Any idea would be very much appreciated.</p>
  <p>Tables:</p>
  <pre><code>--Create a target table
  CREATE TABLE Products
  (
  ProductID INT PRIMARY KEY,
  ProductName VARCHAR(100),
  Rate MONEY
  ) 
  GO
  --Insert records into target table
  INSERT INTO Products
  VALUES
  (1, 'Tea', 10.00),
  (2, 'Coffee', 20.00),
  (3, 'Muffin', 30.00),
  (4, 'Biscuit', 40.00)
  GO
  --Create source table
  CREATE TABLE UpdatedProducts
  (
  ProductID INT PRIMARY KEY,
  ProductName VARCHAR(100),
  Rate MONEY
  ) 
  GO
  --Insert records into source table
  INSERT INTO UpdatedProducts
  VALUES
  (1, 'Tea', 10.00),
  (2, 'Coffee', 25.00),
  (3, 'Muffin', 35.00),
  (5, 'Pizza', 60.00)
  GO
  SELECT * FROM Products
  SELECT * FROM UpdatedProducts
  GO
  </code></pre>