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:
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!
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!