How do I use an INSERT statement's OUTPUT clause to get the identity value?

Practical applications and best practices

Executing with a Client Application

If you're integrated with a client application, roll out the INSERT query using .ExecuteScalar() to grab the identity value:

DECLARE @NewID int;

INSERT INTO TargetTable (Col1)
OUTPUT INSERTED.IdentityColumn INTO @NewID
VALUES ('Data');

-- In App Code
int id = sqlCommand.ExecuteScalar();  // "Look, ma! I got an int!"


Conquering Multiple Identity Values

When inputting multiple records and catching their identities, call in for backup from a temporary or a persistent table:

CREATE TABLE #TempIDs (ID int);

INSERT INTO TargetTable (Col1, Col2, ...)
OUTPUT INSERTED.IdentityColumn INTO #TempIDs
VALUES ('Data1', 'Data2', ...),
       ('Data3', 'Data4', ...),
       ...;

SELECT ID FROM #TempIDs;  // "Who ya gonna call? Temp IDs!"


Precision in Complex Inserts

Aiming a JOIN operation in an INSERT command? Target the right identity values among a crowd of rows:

INSERT INTO TargetTable (Col1, Col2, ...)
OUTPUT INSERTED.IdentityColumn, INSERTED.Col1, INSERTED.Col2 INTO @ID(NewID, Col1, Col2)
SELECT SourceTable.Col1, SourceTable.Col2, ...
FROM SourceTable
WHERE SourceTable.Col1 = 'Criteria'; // "WHERE's Waldo... I mean ID"


Curating from real-world cases

Auditor's Dreams

In audit tracking, where you dock changes, OUTPUT clause shoots two birds with one stone—direct logging of pre and post versions:

DECLARE @AuditTable TABLE (ActionType nvarchar(100), OldID int, NewID int);

INSERT INTO TargetTable (Col1)
OUTPUT 'INSERTED', NULL, INSERTED.IdentityColumn INTO @AuditTable
VALUES ('Data');

-- Holds a log of actions paired with their identity values


Solve Conditional Insert Puzzles

In situations having conditional logic, the OUTPUT clause helps solve the puzzle by dynamically capturing identity values:

IF NOT EXISTS(SELECT * FROM TargetTable WHERE Col1 = 'SomeData')
BEGIN
    INSERT INTO TargetTable (Col1)
    OUTPUT INSERTED.IdentityColumn INTO @ID
    VALUES ('SomeData');
END

SELECT * FROM @ID;  // "If it exists, '@ID' did it!"


Performance First!

In high-volume data operations, keep an eye on the performance. Saving identity values in a table variable usually wins a race against a temporary table, but hey, it also depends on how massive the data is and your hardware ammo.