Archives

Using the Output Clause with T-SQL Merge

The Output clause, first implemented in SQL Server 2005, can be used to return information for each row modified by an Insert, Update, Delete or Merge statement. This functionality greatly increases the power and usefulness of Merge, and is required in the processing of Slowly Changing Dimensions.

This post is the second in a series called Have You Got the Urge to Merge? and is a follow up to Writing T-SQL Merge Statements the Right Way. If you just happened upon this article, feel free to jump to the beginning and follow along through the entire series.

As usual, I feel the easiest way to learn something is by example. For the sake of simplicity, I’m going to continue using the same tables and code from the first article in the series. To set up, we’ll run the following code:

 USE TempDb;
GO
 
IF OBJECT_ID ('tempdb..#Customer_Orig') IS NOT NULL DROP TABLE #Customer_Orig;
IF OBJECT_ID ('tempdb..#Customer_New')  IS NOT NULL DROP TABLE #Customer_New;
 
 
CREATE TABLE #Customer_Orig
(  CustomerNum    TINYINT NOT NULL
  ,CustomerName   VARCHAR (25) NULL
  ,Planet         VARCHAR (25) NULL);
 
CREATE TABLE #Customer_New
(  CustomerNum    TINYINT NOT NULL
  ,CustomerName   VARCHAR (25) NULL
  ,Planet         VARCHAR (25) NULL);
 
INSERT INTO #Customer_New (CustomerNum, CustomerName, Planet)
   VALUES (1, 'Anakin Skywalker', 'Tatooine')
         ,(2, 'Yoda', 'Coruscant')
         ,(3, 'Obi-Wan Kenobi', 'Coruscant');  
 
INSERT INTO #Customer_Orig (CustomerNum, CustomerName, Planet)
   VALUES (2, 'Master Yoda', 'Coruscant')
         ,(3, 'Obi-Wan Kenobi', 'Coruscant')
         ,(4, 'Darth Vader', 'Death Star');
 
SELECT * FROM #Customer_Orig Order by CustomerNum;
SELECT * FROM #Customer_New Order by CustomerNum;

When you run the code above, you should have the following tables. Remember that in the previous example, Customer_Orig and Customer_New started off being identical. I’ve skipped ahead to the point where the following changes were made to Customer_Orig in preparation for the Merge demo.

  1. The Darth Vader record was added to Customer_Orig.
  2. Yoda’s name was changed.
  3. Anakin Skywalker was deleted from Customer_Orig. I know that in the following screenshot I show the record in the table. I included it, and formatted it to suggest that it was once there but it has been deleted.The effect will be to delete Anakin Skywalker from Customer_New.

Merge Changes To Base
Customer New

So, an appropriate Merge statement for these tables can be taken from the previous article as well. However, this time we will add an Output clause in its most basic form. I’ve used the T-SQL comment marks to separate the new section and help it stand out.

MERGE  #Customer_New AS Target
 USING #Customer_Orig AS Source
    ON Target.CustomerNum = Source.CustomerNum
WHEN MATCHED AND EXISTS
                    (SELECT Source.CustomerName, Source.Planet
                     EXCEPT
                     SELECT Target.CustomerName, Target.Planet)
THEN
   UPDATE SET
      Target.CustomerName = Source.CustomerName
     ,Target.Planet = Source.Planet
WHEN NOT MATCHED BY TARGET
THEN
   INSERT (CustomerNum, CustomerName, Planet)
   VALUES (CustomerNum, Source.CustomerName, Source.Planet)
WHEN NOT MATCHED BY SOURCE THEN DELETE
-------------------------------------
OUTPUT $action, inserted.*, deleted.*
-------------------------------------
;

When that code is run, you’ll receive the following:
basic_output

The results table may look confusing, but it’ll make sense in a minute. First, the word OUTPUT is essentially a substitute for SELECT. Second, the $action variable obviously indicates the type of action performed on that row. The actions are Insert, Update, or Delete. If you’ve ever used Triggers you’ll know that they work the same way. When a record is modified two temporary tables are created for Inserted and Deleted values per record. If a record is updated, then it has a record in both of those tables.

Knowing this, you can interpret the results and see that our merge statement was effective and did make Customer_New identical to Customer_Orig. So, if you re-run the select statements from above, you see the following:
Result of Merge

Keep in mind that you don’t have to simply Output the values to the screen. You could insert those records into a physical table, temp table, or table variable as well. Next, let’s insert them into another Temp Table. Re-run the setup code above with the following modifications.

IF OBJECT_ID( 'tempdb..#CustomerChanges') IS NOT NULL DROP TABLE #CustomerChanges;
 
CREATE TABLE #CustomerChanges(
  ChangeType         NVARCHAR(10)
 ,CustomerNum        TINYINT NOT NULL
 ,NewCustomerName    VARCHAR(25) NULL
 ,PrevCustomerName   VARCHAR(25) NULL
 ,NewPlanet          VARCHAR(25) NULL
 ,PrevPlanet         VARCHAR(25) NULL
 ,UserName           NVARCHAR(100) NOT NULL
 ,DateTimeChanged    DateTime NOT NULL);
 
MERGE  #Customer_New AS Target
 USING #Customer_Orig AS Source
    ON Target.CustomerNum = Source.CustomerNum
WHEN MATCHED AND EXISTS
                    (SELECT Source.CustomerName, Source.Planet
                     EXCEPT
                     SELECT Target.CustomerName, Target.Planet)
THEN
   UPDATE SET
      Target.CustomerName = Source.CustomerName,
      Target.Planet = Source.Planet
WHEN NOT MATCHED BY TARGET
THEN
   INSERT (CustomerNum, CustomerName, Planet)
   VALUES (Source.CustomerNum, Source.CustomerName, Source.Planet)
WHEN NOT MATCHED BY SOURCE THEN DELETE
-------------------------------------
OUTPUT
   $ACTION ChangeType,
   coalesce (inserted.CustomerNum, deleted.CustomerNum) CustomerNum,
   inserted.CustomerName NewCustomerName,
   deleted.CustomerName PrevCustomerName,
   inserted.Planet NewPlanet,
   deleted.Planet PrevPlanet,
   SUSER_SNAME() UserName,
   Getdate () DateTimeChanged
    INTO #CustomerChanges
-------------------------------------
;
 
SELECT * FROM #CustomerChanges;

Output Refined
I added the last two columns because I often use the Output clause with Merge (and other DML statements) for auditing purposes.

Now that we’ve covered the basics of Merge and the Output clause, our next article will cover how to use both to process Slowly Changing Dimensions.

Related posts:

6 comments to Using the Output Clause with T-SQL Merge

Leave a Reply

 

 

 

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>