sql - value - Best way to get identity of inserted row?
sql server get identity value after insert (8)
@@IDENTITY, SCOPE_IDENTITY, and IDENT_CURRENT are similar functions in that they return the last value inserted into the IDENTITY column of a table.
@@IDENTITY and SCOPE_IDENTITY will return the last identity value generated in any table in the current session. However, SCOPE_IDENTITY returns the value only within the current scope; @@IDENTITY is not limited to a specific scope.
IDENT_CURRENT is not limited by scope and session; it is limited to a specified table. IDENT_CURRENT returns the identity value generated for a specific table in any session and any scope. For more information, see IDENT_CURRENT.
What is the best way to get
IDENTITY of inserted row?
I know about
SCOPE_IDENTITY but don't understand the pros and cons attached to each.
Can someone please explain the differences and when I should be using each?
@@IDENTITY is the last identity inserted using the current SQL Connection. This is a good value to return from an insert stored procedure, where you just need the identity inserted for your new record, and don't care if more rows were added afterward.
SCOPE_IDENTITY is the last identity inserted using the current SQL Connection, and in the current scope -- that is, if there was a second IDENTITY inserted based on a trigger after your insert, it would not be reflected in SCOPE_IDENTITY, only the insert you performed. Frankly, I have never had a reason to use this.
IDENT_CURRENT(tablename) is the last identity inserted regardless of connection or scope. You could use this if you want to get the current IDENTITY value for a table that you have not inserted a record into.
SELECT CAST(scope_identity() AS int);
to the end of your insert sql statement, then
NewId = command.ExecuteScalar()
will retrieve it.
After Your Insert Statement you need to add this. And Make sure about the table name where data is inserting.You will get current row no where row affected just now by your insert statement.
I can't speak to other versions of SQL Server, but in 2012, outputting directly works just fine. You don't need to bother with a temporary table.
INSERT INTO MyTable OUTPUT INSERTED.ID VALUES (...)
By the way, this technique also works when inserting multiple rows.
INSERT INTO MyTable OUTPUT INSERTED.ID VALUES (...), (...), (...)
ID 2 3 4
I'm saying the same thing as the other guys, so everyone's correct, I'm just trying to make it more clear.
@@IDENTITY returns the id of the last thing that was inserted by your client's connection to the database.
Most of the time this works fine, but sometimes a trigger will go and insert a new row that you don't know about, and you'll get the ID from this new row, instead of the one you want
SCOPE_IDENTITY() solves this problem. It returns the id of the last thing that you inserted in the SQL code you sent to the database. If triggers go and create extra rows, they won't cause the wrong value to get returned. Hooray
IDENT_CURRENT returns the last ID that was inserted by anyone. If some other app happens to insert another row at an unforunate time, you'll get the ID of that row instead of your one.
If you want to play it safe, always use
SCOPE_IDENTITY(). If you stick with
@@IDENTITY and someone decides to add a trigger later on, all your code will break.
When you use Entity Framework, it internally uses the
OUTPUT technique to return the newly inserted ID value
DECLARE @generated_keys table([Id] uniqueidentifier) INSERT INTO TurboEncabulators(StatorSlots) OUTPUT inserted.TurboEncabulatorID INTO @generated_keys VALUES('Malleable logarithmic casing'); SELECT t.[TurboEncabulatorID ] FROM @generated_keys AS g JOIN dbo.TurboEncabulators AS t ON g.Id = t.TurboEncabulatorID WHERE @@ROWCOUNT > 0
The output results are stored in a temporary table variable, joined back to the table, and return the row value out of the table.
Note: I have no idea why EF would inner join the ephemeral table back to the real table (under what circumstances would the two not match).
But that's what EF does.
This technique (
OUTPUT) is only available on SQL Server 2008 or newer.
@@IDENTITYreturns the last identity value generated for any table in the current session, across all scopes. You need to be careful here, since it's across scopes. You could get a value from a trigger, instead of your current statement.
SCOPE_IDENTITY()returns the last identity value generated for any table in the current session and the current scope. Generally what you want to use.
IDENT_CURRENT('tableName')returns the last identity value generated for a specific table in any session and any scope. This lets you specify which table you want the value from, in case the two above aren't quite what you need (very rare). Also, as @Guy Starbuck mentioned, "You could use this if you want to get the current IDENTITY value for a table that you have not inserted a record into."
OUTPUTclause of the
INSERTstatement will let you access every row that was inserted via that statement. Since it's scoped to the specific statement, it's more straightforward than the other functions above. However, it's a little more verbose (you'll need to insert into a table variable/temp table and then query that) and it gives results even in an error scenario where the statement is rolled back. That said, if your query uses a parallel execution plan, this is the only guaranteed method for getting the identity (short of turning off parallelism). However, it is executed before triggers and cannot be used to return trigger-generated values.