1

Am I able to use the OUTPUT inserted.identity value obtained after an insert during an SQL transaction (or does that even return a value)?

Here's pseudo-code to explain the situation

Begin SQLTransaction (Using .NET SQLTransaction)
- Insert into table output identity field of inserted
- Insert into another table using the above command's output identity.
End transaction

I'm worried that since this is in a transaction and the first insert query is not actually committed to the database, the outputted identity column of the first command will possibly not be valid.

Is there a guarantee that the output identity of an INSERT during a transaction will be the same as what is committed after the transaction? Or does SQL prohibit OUTPUT INSERTED.x during transactions?

Fuzzy Logic
  • 13
  • 4
  • `OUTPUT` is actually rather special in that it is produced "inline" -- even if the transaction is rolled back, the output will still have been sent, and clients have to make sure to discard/ignore it in that case. If the transaction is committed, though, there's no problem. – Jeroen Mostert Feb 21 '18 at 21:29

1 Answers1

1

Yes, the OUTPUT clause will behave exactly like SCOPE_IDENTITY().

After the first INSERT statement, the record will have been inserted into the database, just not yet committed. At this point the identity will never change.

If the transaction were to be rolled back however, the identity will be lost forever (leaving a gap)

Lennart Stoop
  • 1,649
  • 1
  • 12
  • 18