THE SQL Server Blog Spot on the Web

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

Jamie Thomson

This is the blog of Jamie Thomson, a freelance data mangler in London

MERGE and OUTPUT – the swiss army knife of T-SQL

The new T-SQL MERGE statement in SQL Server 2008 seems to be finding many uses over and above its de facto UPSERT usage scenario, probably the most popular of which is its ability to update one table from another in an ANSI-compliant manner as detailed expertly by Hugo Kornelius in his blog post Let's deprecate UPDATE FROM!

Today I stumbled upon a different use for it, returning values using an OUTPUT clause from a table used as the source of data for an insertion. In other words, if I’m inserting from [tableA] into [tableB] then I may want some values from [tableA] after the fact, particularly if [tableB] has an identity. Observe my first attempt using a straight insertion where I am trying to get a field from @source.[id] that is not used in the insertion:

DECLARE @source TABLE (

  
[id] INT PRIMARY KEY
[name] VARCHAR(10)
);
INSERT @source VALUES(1000,'Harold'),(2000,'Madge');
DECLARE @destination TABLE (
  
[id] INT PRIMARY KEY IDENTITY(1,1)
NAME VARCHAR(10)
);

INSERT @destination ([name])
OUTPUT INSERTED.[id] AS NEWID,[source].[id] AS OldId
SELECT [name]
FROM   @source;

This failed with the error:

image

The reason this fails is that the INSERT statement has no FROM clause thus the OUTPUT clause can never know about where the data is sourced from; the FROM clause in the SELECT that is used to source the data for the insertion is not actually part of the INSERT statement. Let’s try this with a MERGE instead:

DECLARE @source TABLE (

  
[id] INT PRIMARY KEY
[name] VARCHAR(10)
);
INSERT @source VALUES(1000,'Harold'),(2000,'Madge');
DECLARE @destination TABLE (
  
[id] INT PRIMARY KEY IDENTITY(1,1)
NAME VARCHAR(10)
);

MERGE  @destination
USING  (SELECT [id], [name] FROM @source) AS [source]
ON     (1=0) --arbitrary join condition
WHEN   NOT MATCHED THEN
       INSERT
(name)
      
VALUES  (source.Name)
      
OUTPUT  INSERTED.id AS NEWID,[source].[id] AS OldId,INSERTED.name;

This worked!

image

Notice how we are able to reference the source of our data in the OUTPUT clause of of our MERGE statement which we couldn’t do when using INSERT. Pretty cool huh!

I remembered while writing this that Adam Machanic has already documented this capability in his blog post Dr. OUTPUT or: How I Learned to Stop Worrying and Love the MERGE and he has covered it in a lot more detail than I have here but still, I see no harm in reiterating and the code above is an easily executable demo of this cool little feature.

@Jamiet 

N.B. Thanks to Aaron Bertrand for his instructions here on producing well-formatted T-SQL code for blogs! I’m putting this comment here so I know where to find it next time!

Published Wednesday, January 06, 2010 7:46 PM by jamiet
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

Comments

 

MontyMole said:

Would probably be better if the last line of the first example was changed from

FROM   @source;

to

FROM   @source as [source];

As [source] isn't referenced anywhere in the first example.

Same result though, even after this change.  Just reads a little better.

January 7, 2010 11:38 AM
 

Vic Kirkpatrick said:

I just don't understand why you can't get the source values when using OUTPUT with a normal INSERT statement, but you definitely can't. Very frustrating. You can only access the INSERT.* columns. Arrgghhh. You can't access non-INSERTED.* column even if you do use a FROM clause in the insert!

October 20, 2010 2:43 PM
 

MrSquish said:

Nice workaround.  Another workaround is doing a alter on the table and adding an extra column temporarily to insert the source.id value.  Merge is definitely much cleaner though.

July 20, 2011 2:39 AM
 

Pete Mack said:

I've been using this model for a while now in SSIS tasks.

It's a real shame there's no native support for MERGE, because using it seems to require creating a temporary table in the destination database (not in destination DB Server tempDB.)

The trouble with this is that the SSIS task requires DBO permissions to run.

I suppose I could write a stored procedure on the destination db that creates an appropriate temp table, but it's a really dirty model.

Any suggestions?

Here's my flow:

1. Drop temp table (if it exists)

2. Create temp table with SRC table schema (named by current date)

3. Transfer source data into temp table with ordinary bulk insert model.

4. Perform MERGE operation on temp table with destination table

5. Delete temp table.

April 5, 2012 10:43 PM
 

Phil Cooper said:

Genius.  I've always edged from using the merge statement as I didn't quite look the like of the syntax.  This little technique means we are now friends.  It solves a problem I've encountered so many times, so gracefully. A+

October 16, 2012 3:13 AM
 

aaa bbb said:

Thank you so much for this article. I had this exact problem you described above with the 'multi-part identifier' not being bound....and the merge/output solution worked perfectly.

December 24, 2012 2:46 PM
 

Werner said:

is it not easier to use delelted.ID?

There are two outputs, like in triggers: inserted = the newe record and deleted = the record before any changes

December 2, 2013 7:08 AM
 

Thierry said:

This is fantastic.

Thank you, you really helped me out with this.

January 29, 2014 5:08 AM
 

Tejas said:

Is there a way to send the result of the OUTPUT Command to a flat file?

November 3, 2014 2:26 PM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement