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.

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

  • Bruce W Cassidy

    I ran into a recent issue with using merge/output combined with an insert, intending to do an SCD load with one effective statement.

    It turns out that if you have a foreign key to the dimension, SQL Server will not process the statement. I had to break it into the merge with the output going to a temporary table, and then doing an insert from there.

  • Bruce, thanks for the comment. That’s in my next article about handing SCD loads with merge, which is being proof-read at the moment. I should publish it later today or tomorrow.

  • Psalm

    Thank you for uploading. This is one of the best series that gave good and clear explanation with practical examples of T-SQL Merge. Thanks alot

  • Andrey

    Hello David,

    thanks for this areticle. Very good explanation.

    Greetings from Hamburg/Germany,
    Andrey

  • Thank you very much. Helped me in understanding the OUTPUT clause with $action.

  • Steve

    David, excellent articles on the MERGE statement! Especially using the OUTPUT clause.

  • Nick D

    Nice article thanks

    What does this do?

    , inserted.*, deleted.*

    The output seems the same whether I have

    OUTPUT $action, inserted.*, deleted.*;

    or just

    OUTPUT $action

    thanks.

  • Ganesh

    I’m using the below Merge SQL to insert data and maintain a Audit Table for DML tracking, but the Output clause is failing.

    –MERGE SQL statement – Part 2
    –Synchronize the target table with
    –refreshed data from source table
    MERGE dbo.apps AS TARGET
    USING stg.apps AS SOURCE
    ON (TARGET.ApplicationCode = SOURCE.ApplicationCode)
    –When records are matched
    –Update the records if there is any change
    WHEN MATCHED AND TARGET.ApplicationName SOURCE.ApplicationName
    OR TARGET.ApplicationOwnerBusiness SOURCE.ApplicationOwnerBusiness
    OR TARGET.ApplicationownerIT SOURCE.ApplicationownerIT
    OR TARGET.RTO SOURCE.RTO
    OR TARGET.RPO SOURCE.RPO
    OR TARGET.InScopeDR SOURCE.InScopeDR
    THEN
    UPDATE SET TARGET.ApplicationName=SOURCE.ApplicationName,
    TARGET.ApplicationOwnerBusiness=SOURCE.ApplicationOwnerBusiness,
    TARGET.ApplicationownerIT=SOURCE.ApplicationownerIT,
    TARGET.RTO=SOURCE.RTO,
    TARGET.RPO=SOURCE.RPO,
    TARGET.InScopeDR=SOURCE.InScopeDR
    –When no records are matched
    –Insert the incoming records from source
    –table to target table
    WHEN NOT MATCHED BY TARGET THEN
    INSERT (ApplicationCode
    ,ApplicationName
    ,InScopeDR
    ,ApplicationVersion
    ,ApplicationAlias
    ,ApplicationDescription
    ,ApplicationType
    ,ApplicationArchitecture
    ,ApplicationVendor
    ,RTO
    ,RPO
    ,IsCOTS
    ,IsCritical
    ,BusinessUnit
    ,ApplicationOwnerIT
    ,ApplicationOwnerBusiness
    ,ApplicationSME
    ,HADesign
    ,LicenseRequirements
    ,InScopeMigration
    ,Notes
    )
    VALUES (Source.ApplicationCode,
    Source.ApplicationName,
    Source.InScopeDR,
    Source.ApplicationVersion,
    Source.ApplicationAlias,
    Source.ApplicationDescription,
    Source.ApplicationType,
    Source.ApplicationArchitecture,
    Source.ApplicationVendor,
    Source.RTO,
    Source.RPO,
    Source.IsCOTS,
    Source.IsCritical,
    Source.BusinessUnit,
    Source.ApplicationOwnerIT,
    Source.ApplicationOwnerBusiness,
    Source.ApplicationSME,
    Source.HADesign,
    Source.LicenseRequirements,
    Source.InScopeMigration,
    Source.Notes
    )
    OUTPUT
    inserted.ApplicationName NewApplicationName,
    inserted.ApplicationOwnerBusiness NewApplicationOwnerBusiness,
    inserted.ApplicationOwnerIT NewApplicationOwnerIT,
    inserted.RTO NewRTO,
    inserted.RPO NewRPO,
    inserted.InScopeDR NewInScopeDR,
    SUSER_SNAME() as UserName,
    Getdate() DateTimeChanged
    INTO audit.AppicationChangeLog;

    Encountering Error ‘Msg 213, Level 16, State 1, Line 4
    Column name or number of supplied values does not match table definition.’

    Seems it’s not a related error. Above the Output clause is working fine.

    Kindly help me to resolve the issue.

Leave a Reply to Nick D

 

 

 

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>