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.