Using the Merge Statement’s OUTPUT Clause to Match Inserted IDENTITY Fields with Source Table Fields

With this project I needed to insert data into table1 from table2 while simultaneously retrieving the identity field of table1, then insert that identity into table3 which mapped to table2 on a key that table1 did not have.

The OUTPUT clause when used with the input statment would not facilitate this because it could not return the identity and the source key in the same row. The result was to use the SQL Server 2008 Merge statement with the output clause.

Traditionally I would have looped the insert one record at a time and returned back both the values into variables, then inserted the identity into table3 by joining the keys on table2 and table 3.

[cc lang=”sql”]

CREATE TABLE #MatchIDs
(
Table_1_EmployeeID int,
Table_2_EmployeeID int
)

INSERT INTO #MatchIDs
(
Table_1_EmployeeID,
Table_2_EmployeeID
)
SELECT
mrg.Table_1_EmployeeID
,mrg.Table_2_EmployeeID
FROM(
MERGE dbo.Table_1 t1
USING dbo.Table_2 t2
ON 1 = 0
WHEN NOT MATCHED
THEN INSERT
(
FirstName
)
VALUES
(
t2.FirstName
)
OUTPUT
$action
,t2.EmployeeID
,t1.EmployeeID
) AS mrg
(
output_action
,Table_1_EmployeeID
,Table_2_EmployeeID
)
WHERE mrg.output_action = ‘INSERT’
[/cc]

The result is a statement that actually fools the MERGE by intentionally not matching any rows and just inserting all the records into the table.

6 comments
Vincent D'Souza 20 Jan 2012 at 12:38 pm

Here is the simplied version of that… enjoy…

CREATE TABLE #MatchIDs
(
UpdateAction varchar(50),
Table_1_EmployeeID int,
Table_2_EmployeeID int
)

MERGE INTO Table_1 AS TARGET
USING Table_2 AS SOURCE
ON TARGET.id = SOURCE.id
WHEN MATCHED THEN
UPDATE SET TARGET.FristName = SOURCE.FIRSTNAME
, TARGET.LASTNAME = SOURCE.LASTNAME
WHEN NOT MATCHED THEN
INSERT (FirstName, LASTNAME)
VALUES (SOURCE.FIRSTNAME, SOURCE.LASTNAME)
OUTPUT $action, t2.EmployeeID ,t1.EmployeeID into #MatchIDs;

Varun Varghese 22 Dec 2011 at 5:27 am

I tried this, but I am getting the following error: Msg 4104, Level 16, State 1, Line 30
The multi-part identifier “t1.EmployeeID” could not be bound.

Any ideas what could be wrong. Thanks in advance.

Srikanth 08 Mar 2012 at 5:07 am

Please try ‘Inserted.EmployeeID’ instead of ‘t1.EmployeeID’

fotis 25 Feb 2011 at 4:27 am

briliant piece of code!

Ben 18 Feb 2010 at 8:58 am

Hi,

I really need to do this but am using SQL 2005 – is there any way that this can be done?

Derek D. 07 Apr 2010 at 8:16 am

Hi Ben, sorry for the late reply. Unfortunately I don’t think there is.. At least that I know of.

Featured Articles

 Site Author

  • Thanks for visiting!