THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Rob Farley

- Owner/Principal with LobsterPot Solutions (a MS Gold Partner consulting firm), Microsoft Certified Master, Microsoft MVP (SQL Server), APS/PDW trainer and leader of the SQL User Group in Adelaide, Australia. Rob is a former director of PASS, and provides consulting and training courses around the world in SQL Server and BI topics.

MERGE gives better OUTPUT options

MERGE is very cool. There are a ton of useful things about it – mostly around the fact that you can implement a ton of change against a table all at once. This is great for data warehousing, handling changes made to relational databases by applications, all kinds of things.

One of the more subtle things about MERGE is the power of the OUTPUT clause. Useful for logging.


If you’re not familiar with the OUTPUT clause, you really should be – it basically makes your DML (INSERT/DELETE/UPDATE/MERGE) statement return data back to you. This is a great way of returning identity values from INSERT commands (so much better than SCOPE_IDENTITY() or the older (and worse) @@IDENTITY, because you can get lots of rows back). You can even use it to grab default values that are set using non-deterministic functions like NEWID() – things you couldn’t normally get back without running another query (or with a trigger, I guess, but that’s not pretty).

That inserted table I referenced – that’s part of the ‘behind-the-scenes’ work that goes on with all DML changes. When you insert data, this internal table called inserted gets populated with rows, and then used to inflict the appropriate inserts on the various structures that store data (HoBTs – the Heaps or B-Trees used to store data as tables and indexes). When deleting, the deleted table gets populated. Updates get a matching row in both tables (although this doesn’t mean that an update is a delete followed by an inserted, it’s just the way it’s handled with these tables). These tables can be referenced by the OUTPUT clause, which can show you the before and after for any DML statement. Useful stuff.

MERGE is slightly different though.

With MERGE, you get a mix of entries. Your MERGE statement might be doing some INSERTs, some UPDATEs and some DELETEs. One of the most common examples of MERGE is to perform an UPSERT command, where data is updated if it already exists, or inserted if it’s new. And in a single operation too. Here, you can see the usefulness of the deleted and inserted tables, which clearly reflect the type of operation (but then again, MERGE lets you use an extra column called $action to show this).


(Don’t worry about the fact that I turned on IDENTITY_INSERT, that’s just so that I could insert the values)

One of the things I love about MERGE is that it feels almost cursor-like – the UPDATE bit feels like “WHERE CURRENT OF …”, and the INSERT bit feels like a single-row insert. And it is – but into the inserted and deleted tables. The operations to maintain the HoBTs are still done using the whole set of changes, which is very cool.

And $action – very convenient.

But as cool as $action is, that’s not the point of my post. If it were, I hope you’d all be disappointed, as you can’t really go near the MERGE statement without learning about it.

The subtle thing that I love about MERGE with OUTPUT is that you can hook into more than just inserted and deleted.

Did you notice in my earlier query that my source table had a ‘src’ field, that wasn’t used in the insert? Normally, this would be somewhat pointless to include in my source query. But with MERGE, I can put that in the OUTPUT clause.


This is useful stuff, particularly when you’re needing to audit the changes. Suppose your query involved consolidating data from a number of sources, but you didn’t need to insert that into the actual table, just into a table for audit. This is now very doable, either using the INTO clause of OUTPUT, or surrounding the whole MERGE statement in brackets (parentheses if you’re American) and using a regular INSERT statement.

This is also doable if you’re using MERGE to just do INSERTs.

In case you hadn’t realised, you can use MERGE in place of an INSERT statement. It’s just like the UPSERT-style statement we’ve just seen, except that we want nothing to match. That’s easy to do, we just use ON 1=2.

This is obviously more convoluted than a straight INSERT. And it’s slightly more effort for the database engine too. But, if you want the extra audit capabilities, the ability to hook into the other source columns is definitely useful.

Oh, and before people ask if you can also hook into the target table’s columns... Yes, of course. That’s what deleted and inserted give you.

Published Tuesday, June 12, 2012 12:40 PM by Rob Farley
Filed under: ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



Greg Lucas said:


Great post, I use the OUTPUT clause quite regularly - especially to populate rollback tables on production data changes but I never knew about $action or the ability to reference other columns from the WITH block.


June 12, 2012 1:56 AM

Rob Farley said:

Thanks Greg. And it's worth remembering that this is only available with MERGE - the other commands only let you hook into inserted and deleted.

June 12, 2012 2:15 AM

SomewhereSomehow said:

Hi Rob. Very interesting scenario of using merge for audit. Goes to my KB.

Maybe, it is worth saying, the case of using merge for insert is very convenient to get rid of cursors when copying linked entities. I blogged about it sometime ago. For example, if we have classical scheme: Customer<- Order <- OrderDetail and a need to copy orders from one client to another. With help of merge we may get a table of links: (OrderID_old, OrderID_new). And after that, use it in join, to copy all the order details at once, in one single query.

June 13, 2012 3:01 AM

Ilmar said:

Very nice article!  I came accross it while searching for an elegant solution to a problem very similar to the one SomewhereSomewhat describes.  Definately added to my toolkit.

June 14, 2012 7:32 PM

Rob Farley said:

Yes, definitely.

June 14, 2012 7:38 PM

Rob Farley said:

Ilmar - you're more than welcome. Good you found it helpful.

June 14, 2012 7:39 PM

Deege said:

Hi Rob,

Is multiple 'OUTPUT INTO' clauses allowed


OUTPUT Deleted.*

INTO @oldValues

OUTPUT Inserted.*

INTO @newValues

WHERE id = @id

Currently using


OUTPUT Deleted.*

INTO @oldValues

OUTPUT Inserted.*

INTO @newValues

WHERE id = @id;

INSERT @newValues...


FROM   ..

WHERE  id = @id

I am able to do a OUTPUT INTO and OUTPUT but gives an error when I use multiple OUTPUT INTO

August 15, 2012 7:02 PM

Rob Farley said:

Hi Deege,

This is correct. You can use only one OUTPUT .. INTO and only one OUTPUT clause. But you can use one of each in a single query.


August 16, 2012 1:40 AM

Lally said:

Rob, you are a god.

Well done on being the only person on the net to point this out. Saved me hours.

February 5, 2013 8:39 AM

Rob Farley said:

Not a god. Just happy to help.

February 5, 2013 3:21 PM

Shane said:

Rob thanks for the reminder about $action, I often forget that is available. However I wanted to make one correction. The only DML statement that you can't use source data in the output clause is the INSERT statement. You can capture data form the source for UPDATE and DELETE statements as well as MERGE. It is also important to point out that you can add variables, functions, and constants in the output clause caolumns as well. Here are examples:



Color VARCHAR(50)





Color VARCHAR(50),

DateModified DATE,

[Action] VARCHAR(50)





PreviousColor VARCHAR(50),

NewColor VARCHAR(50),

OrignalDateModified DATE,

DateModified DATE,

[Action] VARCHAR(50)




@Colors (Color)







@DataSource (Color, DateModified, [Action])


('Yellow', '20130207', 'Update'),

('Red', '20130210', 'Update'),

('', '20130212', 'Delete')





Color = DS.Color











@Colors C

JOIN @DataSource DS


AND DS.[Action] = 'Update'













@Colors C

JOIN @DataSource DS


AND DS.[Action] = 'Delete'





April 15, 2013 11:39 AM

K Aditya Vardhan said:

Hi Rob,,

I am using MERGE Statement in which there are three tablse source target and history table

Source --It acts as an intermediate  stage table

Target --It would be the final table

History-- It would be used as an audit purpose which captures the updated records which also consists of two not null columns

When there is no data in target table and History table the output port is sending the updated records with NULL value for which it is throwing error but actually it is not updating any of records how can i avoid such kind of situation..Its only happening when data in target and History are not available when there is data in target the functionality is working fine

Can you suggest me a solution how to fix this

June 25, 2014 6:07 AM

Rob Farley said:

Hi Aditya - Can you send me your code? My email address is on the right...

June 25, 2014 7:32 AM

Mark Kennedy said:

You can access data other than the inserted/deleted in an output clause, but it has to be joined.

There's examples on the OUTPUT clause MSDN page, example E is where I got the idea to try self join.

but try this:


SET someval += 1

OUTPUT deleted.*, inserted.someval,

FROM #tmp

INNER JOIN #tmp as t ON =

I don't know why exactly, but its probably something to do with not being able to use values on the table directly being changed, but joining to its self seems to work.

I did this on SQL server 2008R2

Saves having to delve into using merge statements if you just want to get other data in an OUTPUT from a simple update.

September 18, 2014 5:59 AM

Rob Farley said:

Thanks Mark - that's an interesting idea. :)

September 26, 2014 1:59 AM

Sean Peffer said:

Thanks for this great explaantion of the OUTPUT/$ACTION - very useful.

I used the MERGE statement to update a SCD2 Dimension and it's great that it can be done in a single step iso multiple steps and temp tables. I now require to capture the Audit info as well and this function helps with this requirement as well. Here's an exmaple used inside a stored proc to return the audit info

DECLARE @Results TABLE(Action VARCHAR(20));

/.......Merge statements here...../

OUTPUT $action INTO @Results;




FROM    (SELECT Action,

    1 ROWS

FROM  @Results) P


FOR  Action IN  ([INSERT], [UPDATE], [DELETE])) AS pvt;

October 3, 2014 5:22 AM

Dipankar Saha said:

Hi Rob,

I was working on a requirement where i need to access inserted.* values and values from the Select List in the output clause for an insert statement. I am working in 2005 so i cannot use merge statement which i know is the other way around but wanted to clear my doubt that its techinically not possible or i am missing something.

PFB, the code part.

      INSERT INTO ExternalEmployeeNumber(ExternalEmployeeNumber,DateUsed)

       SELECT  dateadd(second, ROWVAL, DateVale)




FROM dbo.[EOP_REMs$] B where B.[last name] is not null and B.[first name] is not null and B.[email] is not null

and NOT EXISTS ( SELECT 1 FROM employee E WHERE B.[first name] = E.[firstname]  AND B.[email] = E.[email] AND B.[last name] = E.[lastname] )

       ) X

       OUTPUT INSERTED.ExternalEmployeeNumber , FNAME , LNAME , EMAIL

             INTO #EmployeeNew([Employee#],[FirstName],[LastName],[Email]);

In the code part above, i am using Fname, Lname and Email other than the inserted column.

It is giving me an error , invalid reference of the Fname, Lname, Email column name ?

Can you help me out. As i said i implemented this using Merge and its working but i need to implement this in SQL 2005.

Thanks and Regards


January 14, 2015 3:09 AM

Rob Farley said:

Sorry Dipz, you don't have access to that information in an INSERT statement.

January 14, 2015 3:36 AM

Pete Nightingale said:

Interesting but this method does not actually allow you to pickup the values applied with an IDENTITY column as they so not appear in the INSERTED table.

Suppose I had better write a loop then, good job I am covering a rare event.

March 10, 2015 11:48 AM

Peter Nightingale said:

Correction, the IDENTITY column does appear in the INSERTED table, but not in the target table.

So v useful actually. (ahem)

March 10, 2015 11:53 AM

Rob Farley said:

Hi Pete - yes. :)

March 10, 2015 6:01 PM

Ritesh Patel said:

Hi Rob,

The audit table part of your query does not quiet work as expected. Although it is capturing the changes into #someaudittable it is still also performing the merge action on the #tmp table.

Is there any way to prevent the actual merge on #tmp and just getting the 'proposed' changes into #someaudittable?



October 5, 2016 6:21 AM

Karl Ninh said:

Can you output to a file?

October 25, 2016 12:57 PM

Leave a Comment


This Blog



News? Haven't you read my blog?

My Company

Can't find something?

Contact Me

Twitter: @rob_farley
Skype: rob_farley

MVP (SQL Server)


Adelaide SQL UG

Privacy Statement