How to get the identity of an inserted row?

How am I supposed to get the `IDENTITY` of an inserted row?

I know about `@@IDENTITY` and `IDENT_CURRENT` and `SCOPE_IDENTITY`, but don't understand the implications or impacts attached to each.

Can someone please explain the differences and when I would be using each?

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.

**ALWAYS** use scope_identity(), there's NEVER a need for anything else.

- [`@@IDENTITY`][1] returns 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()`][2] 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')`][3] 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][4] 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."

- The [`OUTPUT` clause][5] of the `INSERT` statement 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.


[1]:

[To see links please register here]

[2]:

[To see links please register here]

[3]:

[To see links please register here]

[4]:

[To see links please register here]

[5]:

[To see links please register here]

Add

SELECT CAST(scope_identity() AS int);

to the end of your insert sql statement, then

NewId = command.ExecuteScalar()

will retrieve it.

**@@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.

The best (read: safest) way to get the identity of a newly-inserted row is by using the `output` clause:

create table TableWithIdentity
( IdentityColumnName int identity(1, 1) not null primary key,
... )

-- type of this table's column must match the type of the
-- identity column of the table you'll be inserting into
declare @IdentityOutput table ( ID int )

insert TableWithIdentity
( ... )
output inserted.IdentityColumnName into @IdentityOutput
values
( ... )

select @IdentityValue = (select ID from @IdentityOutput)

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.

IDENT_CURRENT('tableName')

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
(...),
(...),
(...)

Output

ID
2
3
4

One other way to guarantee the identity of the rows you insert is to specify the identity values and use the `SET IDENTITY_INSERT ON` and then `OFF`. This guarantees you know exactly what the identity values are! As long as the values are not in use then you can insert these values into the identity column.

CREATE TABLE #foo
(
fooid INT IDENTITY NOT NULL,
fooname VARCHAR(20)
)

SELECT @@Identity AS [@@Identity],
Scope_identity() AS [SCOPE_IDENTITY()],
Ident_current('#Foo') AS [IDENT_CURRENT]

SET IDENTITY_INSERT #foo ON

INSERT INTO #foo
(fooid,
fooname)
VALUES (1,
'one'),
(2,
'Two')

SET IDENTITY_INSERT #foo OFF

SELECT @@Identity AS [@@Identity],
Scope_identity() AS [SCOPE_IDENTITY()],
Ident_current('#Foo') AS [IDENT_CURRENT]

INSERT INTO #foo
(fooname)
VALUES ('Three')

SELECT @@Identity AS [@@Identity],
Scope_identity() AS [SCOPE_IDENTITY()],
Ident_current('#Foo') AS [IDENT_CURRENT]

-- YOU CAN INSERT
SET IDENTITY_INSERT #foo ON

INSERT INTO #foo
(fooid,
fooname)
VALUES (10,
'Ten'),
(11,
'Eleven')

SET IDENTITY_INSERT #foo OFF

SELECT @@Identity AS [@@Identity],
Scope_identity() AS [SCOPE_IDENTITY()],
Ident_current('#Foo') AS [IDENT_CURRENT]

SELECT *
FROM #foo

This can be a very useful technique if you are loading data from another source or merging data from two databases etc.