294

If I have an insert statement such as:

INSERT INTO MyTable
(  
  Name,
  Address,
  PhoneNo
)
VALUES
(
  'Yatrix',
   '1234 Address Stuff',
   '1112223333'
)

How do I set @var INT to the new row's identity value (called Id) using the OUTPUT clause? I've seen samples of putting INSERTED.Name into table variables, for example, but I can't get it into a non-table variable.

I've tried OUPUT INSERTED.Id AS @var, SET @var = INSERTED.Id, but neither have worked.

johnnyRose
  • 7,310
  • 17
  • 40
  • 61
Yatrix
  • 13,361
  • 16
  • 48
  • 78
  • 3
    I know about @@SCOPE_IDENTITY already, I specifically want to know how to do it with OUPUT. Thanks. – Yatrix Jun 12 '12 at 15:04
  • 6
    You need to insert it into a table variable then select from that. There is no syntax to assign directly to a scalar variable from the `OUTPUT` clause. – Martin Smith Jun 12 '12 at 15:07
  • 4
    The [OUTPUT clause](http://msdn.microsoft.com/en-us/library/ms177564.aspx) has to output into a table or table variable.. – mellamokb Jun 12 '12 at 15:07
  • 6
    The `OUTPUT` clause writes to a table. It can be a table variable, temporary table, ... . – HABO Jun 12 '12 at 15:07
  • 1
    marc_s answer is correct but this isn't needed to get the identity of the last inserted. I agree to avoid the `@@IDENTITY` because of trigger issues. There is a built-in function just for this case called `SCOPE_IDENTITY()`. Simply replace `@@IDENTITY` with `SCOPE_IDENTITY()` to get the truly inserted identity. – M27 Sep 30 '16 at 14:38
  • 2
    My question **specifically asks** for the OUTPUT clause. – Yatrix Sep 30 '16 at 18:33

1 Answers1

580

You can either have the newly inserted ID being output to the SSMS console like this:

INSERT INTO MyTable(Name, Address, PhoneNo)
OUTPUT INSERTED.ID
VALUES ('Yatrix', '1234 Address Stuff', '1112223333')

You can use this also from e.g. C#, when you need to get the ID back to your calling app - just execute the SQL query with .ExecuteScalar() (instead of .ExecuteNonQuery()) to read the resulting ID back.

Or if you need to capture the newly inserted ID inside T-SQL (e.g. for later further processing), you need to create a table variable:

DECLARE @OutputTbl TABLE (ID INT)

INSERT INTO MyTable(Name, Address, PhoneNo)
OUTPUT INSERTED.ID INTO @OutputTbl(ID)
VALUES ('Yatrix', '1234 Address Stuff', '1112223333')

This way, you can put multiple values into @OutputTbl and do further processing on those. You could also use a "regular" temporary table (#temp) or even a "real" persistent table as your "output target" here.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • 2
    The answer here for the code behind was concise. ExecuteScalar() FTW – Joe Johnston Apr 25 '14 at 15:26
  • 13
    You can insert the result in a `real persistent table` - this is extremely fantastic because it means that you can `INSERT` information in `TWO` tables at the same time. – gotqn Feb 06 '15 at 09:13
  • 7
    Don't ever use @@IDENTITY to pull from the top. Foud out the hard way working with triggers and since they were recording history of changes made to one table and inserting into a new table at the same time @@IDENTITY started returning back values from the history table. hilarity ensues from there! Please use marc_s' solution. for the time being I have went with the @OutputTbl method, but I'm intrigued by the other options. – Eric Bishard Sep 20 '16 at 16:24
  • 6
    OUTPUT INTO is extremely fantastic except that "The target table of the OUTPUT INTO clause cannot be on either side of a (primary key, foreign key) relationship", which for me is about 99% of potential use cases. I assume this is because the OUTPUT clause can return data even when the transaction is rolled back, but it's a little annoying it is so hard to insert into data into related tables A and B in one shot. – Robert Calhoun Nov 10 '16 at 16:32
  • 8
    @EricBishard `SCOPE_IDENTITY()` works better for that. – Derreck Dean Jan 24 '18 at 21:06
  • 1
    @DerreckDean `SCOPE_IDENTITY()` seems to return the last identity *before* the insert, which may be from a different table completely. I've made sure there are no triggers on the INSERT target table, although even with a trigger inserting another record into another table with an identity field, the results are the same. E.g. If I have `OUTPUT inserted.ID, SCOPE_IDENTITY() INTO @OutputTable`, the two values are different. After the second insert, `ID` has a value one greater than `SCOPE_IDENTITY` – Reversed Engineer Apr 23 '18 at 16:33
  • In the case of foreign key constraints (the norm, ha ha Microsoft), I sometimes add another column to the *target* table to receive the source identity field, so I can later join it back. This has an auditing advantage in my applications as well. – Reversed Engineer Apr 23 '18 at 16:40
  • 1
    I believe it is scoped to your connection, and in my experience I have never had it return an ID from a different operation. YMMV, though. – Derreck Dean Apr 24 '18 at 16:39
  • @Yogurtu: the `OUTPUT .... INTO ...` only supports tables - even with just one column - but it cannot be a scalar variable – marc_s Jun 06 '18 at 20:57
  • Not tested this, but I'm betting that why you @DaveBoltman are seeing different numbers is that the SCOPE_IDENTIY() isn't set until after the insert has completed. Thus, you are seeing the value from the previous one (as expected - if you did something like insert into foo (some_field) values (SCOPE_IDENTITY()) - you wouldn't expect that to give you the value you just inserted now would you? No, during the query, it is the value from the previous query.) . Never had SCOPE_IDENTITY() work unexpectedly, although, I LOVE the idea of using Output, and it is awesome in merge commands – Traderhut Games Apr 10 '19 at 20:15
  • 1
    Never use `@@identity`. Never use `scope_identity()`. `output into` is the only way to correctly, atomically, get the identity of the last inserted record. – Ian Kemp Aug 07 '19 at 12:58
  • @marc_s, the solution with `@OutputTbl` was the first solution I've tried (following MS's docs). However, the returned ID is `0` always. My table has an `INSTEAD OF` IUD trigger, which inserts and updates actual values but does not use the idenity column (ID) in those operations as a destination. Any suggestions? – Aleksey F. Dec 05 '19 at 10:02
  • @Robert Calhoun use `MERGE` for that scenario – gfache Aug 07 '20 at 12:28