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

T-SQL
19 Comments

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

  1. Inserting rows into a table that has an identity column, then
  2. 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:

This code pattern is kinda painful because:

  • We lock rows twice (when we read & insert, and then again when we read back what we just did)
  • We have to be careful to only fetch the rows we really just inserted – so we end up putting in all kinds of convoluted logic to work around concurrency problems

Instead, use the OUTPUT clause.

Here’s how to do it without touching the Badges & Users tables twice:

The OUTPUT clause is kinda like the virtual INSERTED/DELETED tables: it lets you grab the output of what you’re doing and redirect it to another place. Presto, less locking, less T-SQL to manage, less guessing as to which rows were affected by your operation.

Isn’t that slick? In my own line of work, I sure don’t need to use it often, but when I do, it’s amazing.

Want to learn more tricks like this?

If you like tricks like this, you’ll love my Mastering Query Tuning class. I’ve got two upcoming sessions: December 11-13 (Fri/Sat/Sun, iCal) and January 12-14 (Tues/Weds/Thurs- iCal.)

If you’ve got a Live Class Season Pass, you can drop in on this class or any of my live online classes at any time. Just head to my current training page, check out the schedule at the bottom of the page, and grab the calendar files for the classes you’re interested in. You don’t have to register ahead of time – just drop in anytime I’m streaming.

Previous Post
Finding the One Query to Tune in a Multi-Query Batch
Next Post
How to Set & Get the Next ID Without Serializable Isolation

19 Comments. Leave new

  • Have to try but triggers on table still prevent using output,right?

    Reply
  • Matthew Monroe
    November 24, 2020 9:43 am

    I discovered while testing this that your screenshot should have “OUTPUT INSERTED.UserId” instead of “OUTPUT INSERTED.Id”. When it didn’t work as-is, my first thought was that my version of SQL Server didn’t support OUTPUT clauses for Insert Into queries, but fixing the typo allowed this to work.

    Reply
    • Matthew Monroe
      November 24, 2020 9:46 am

      Correction; I think this is a SQL Server version issue. I’m testing on SQL Server 2014, and “OUTPUT INSERTED.Id, INSERTED.Name, INSERTED.UserId, INSERTED.[Date]” doesn’t work, with red squiggles under “Id”. Changing it to “OUTPUT INSERTED.UserId, INSERTED.Name, INSERTED.UserId, INSERTED.[Date]” works, but that’s simply me putting UserId into two separate columns.

      Reply
  • Stephen H. Merkel
    November 24, 2020 9:45 am

    Funny you should present the OUTPUT clause today, as I believe it is the solution to yesterday’s post about returning different (not random) results for each query as rows are added.

    Reply
  • That is slick.

    Reply
  • Another reason to use this is if you want to have a read-only asynchronous secondary. If you have an application code pattern where you use a read-write connection for all INSERTs and a read-only connection for all SELECTs, you risk the SELECT not finding any rows because the INSERT hasn’t replicated yet.

    Reply
    • Mark – dude! I love it! I never thought of that, that’s good.

      Reply
      • Yeah, I’m working on an application like this where the architect won’t let us use OUTPUT on INSERTs or the read-only secondary because the dev team would have to change too much code. I wish I had more details, but he said something about it’s because how Entity Framework is set up.

        Reply
  • I am a full time dba and am considering spending my own money to get your consultant’s tool kit for the Black Friday price of $195. You mention in the video on what the output shows that there is a key that expires every 90 days. If I purchase this myself am I going to only be able to use it for 90 days and will the key expire over time (say every year I would need to repurchase the tool). As I have said in every email to you…brilliant work for a truly world class dba and teacher

    Reply
  • Wow! I usually needed to have to the insert in the parent’s table one row at a time to get the identity values and then do the child tables. But with this, I can do all the parents at one time and output the values to get the identity values and then bulk insert the childs. That’s great!

    Reply
  • This is great, Brent. Thanks!

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.

Menu