How to Insert Rows and Get Their Identity Values with the OUTPUT Clause


Say you’ve got a two-step process where you’re:

Inserting rows into a table that has an identity column, then Querying that table to figure out what identities you got

There’s a a faster way that doesn’t require hitting the table twice: the OUTPUT clause.

I’ll demonstrate it with the Badges table from the Stack Overflow database, which has an Id column that’s an identity. I’m going to find all of the Users who live in Iceland, give them a badge, and then return the list of badge IDs I just granted:

INSERT INTO dbo.Badges(Name, UserId, Date) SELECT N’Sunny Disposition’, Id, GETDATE()