Get the last inserted row ID (with SQL statement)

Question

I want to get the new created ID when you insert a new record in table.

I read this: http://msdn.microsoft.com/en-us/library/ms177564.aspx but it needs to create temporary table.

I want to return the ID after executing INSERT statement (assuming executing just one INSERT).

Example:

1     Joe      Joe
2     Michael  Mike
3     Zoe      Zoe

When executing an INSERT statement, I want to return the created ID, means 4.

Can tell me how to do that using SQL statement or it is not possible ?

@Thit what difference does it make if it is the primary key? IDENTITY and primary key are two completely different concepts. While you may often see them associated together, they are not the same thing. I just blogged about this misconception today, actually: https://sqlblog.org/blogs/aaron_bertrand/archive/2012/02/27/bad-habits-to-kick-believing-everything-you-hear-or-read.aspx — Aaron Bertrand, Feb 28 '12 at 06:41

score 302 · Accepted Answer · answered Feb 28 '12 at 06:39

302

If your SQL Server table has a column of type INT IDENTITY (or BIGINT IDENTITY), then you can get the latest inserted value using:

INSERT INTO dbo.YourTable(columns....)
   VALUES(..........)

SELECT SCOPE_IDENTITY()

This works as long as you haven't inserted another row - it just returns the last IDENTITY value handed out in this scope here.

There are at least two more options - @@IDENTITY and IDENT_CURRENT - read more about how they works and in what way they're different (and might give you unexpected results) in this excellent blog post by Pinal Dave here.

answered Feb 28 '12 at 06:39

marc_s

732,580
175
1,330
1,459

9

Note that `SCOPE_IDENTITY()` can yield incorrect results as well (under parallelism), see http://support.microsoft.com/kb/2019779 - the fix was first made available last week for 2008 R2 SP1 CU5. In all earlier versions, the workarounds are to set maxdop to 1, keep a fixed plan that happens to not use parallelism (I have not tested this), or use the output clause. – Aaron Bertrand Feb 28 '12 at 06:43
I suspect Pinal Dave is wrong to say `scope_identity` "will return the last identity value that you explicitly created, rather than any identity that was created by a trigger or a user defined function" - I `insert` into a table with a trigger that causes an insert into an audit table and `scope_identity` returns `null` to me. In fact, the only option that doesn't return `null` is `@@identity` - but it is giving me the audit table's newest ID! So None of these methods return the latest inserted ID in my original insert. SQL Server 2016. – youcantryreachingme Mar 26 '20 at 04:56
@youcantryreachingme: I suspect **you** have an error somewhere in your code - Pinal Dave typically isn't wrong, and most definitely not on this point .. why don't you put your observations into a question and ask it here? – marc_s Mar 26 '20 at 04:57
@marc_s - happy to be shown where my error is. Here is a script that recreates the scenario - https://wetransfer.com/downloads/801bd0c0699b5517eef3e00970bd150220200326052640/2c8e11 – youcantryreachingme Mar 26 '20 at 05:28
2

@youcantryreachingme: well, your table `Client` **does NOT** have any `IDENTITY` column - so a `INSERT INTO Client()...` obviously will ***NOT*** trigger any identity being generated, so `SCOPE_IDENTITY()` will rightfully be NULL :.... – marc_s Mar 26 '20 at 05:41
2

@marc_s thank you for such an emphatic explanation! Wow! I did not expect such emphasis much! It was remiss of me to think that a primary key - which is a unique *identifier* - might be the same as a table's *identity* column (although I did wonder about non `int` and composite PKs). For anyone else tripped up on this, a good explanation is here: https://stackoverflow.com/questions/4293426/whats-the-difference-between-a-primary-key-and-identity – youcantryreachingme Mar 26 '20 at 05:48

Aaron Bertrand · Answer 2 · 2012-02-28T07:02:45.777

69

Assuming a simple table:

CREATE TABLE dbo.foo(ID INT IDENTITY(1,1), name SYSNAME);

We can capture IDENTITY values in a table variable for further consumption.

DECLARE @IDs TABLE(ID INT);

-- minor change to INSERT statement; add an OUTPUT clause:
INSERT dbo.foo(name) 
  OUTPUT inserted.ID INTO @IDs(ID)
SELECT N'Fred'
UNION ALL
SELECT N'Bob';

SELECT ID FROM @IDs;

The nice thing about this method is (a) it handles multi-row inserts (SCOPE_IDENTITY() only returns the last value) and (b) it avoids this parallelism bug, which can lead to wrong results, but so far is only fixed in SQL Server 2008 R2 SP1 CU5.

edited Feb 28 '12 at 07:02

answered Feb 28 '12 at 06:48

Aaron Bertrand

272,866
37
466
490

5

correctness is only important if you view programming as something other than job security through creation of obscure bugs – Eliot Gillum Apr 10 '18 at 04:53
1

I wish there was a shorthand syntax to put the value directly into a variable when you know there is only one result, and I would be happy with it either throwing an error if there are multiple rows (as it does for subqueries in some contexts) or just using the value of the last row (similar to behavior of an update statement that updates a single row based on input of multiple joined rows). – BVernon Jan 06 '19 at 22:56
Would this still be the case within a transaction? – crush Feb 14 '19 at 16:52
@BVernon When you know there will only be one value, there already is shorthand syntax: `SELECT @ID = SCOPE_IDENTITY();` – Aaron Bertrand Sep 11 '20 at 14:43

score 64 · Answer 3 · edited Feb 10 '20 at 17:01

64

You can use:

SELECT IDENT_CURRENT('tablename')

to access the latest identity for a perticular table.

e.g. Considering following code:

INSERT INTO dbo.MyTable(columns....) VALUES(..........)

INSERT INTO dbo.YourTable(columns....) VALUES(..........)

SELECT IDENT_CURRENT('MyTable')

SELECT IDENT_CURRENT('YourTable')

This would yield to correct value for corresponding tables.

It returns the last IDENTITY value produced in a table, regardless of the connection that created the value, and regardless of the scope of the statement that produced the value.

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.

edited Feb 10 '20 at 17:01

Ruudjah

807
7
27

answered Jan 02 '13 at 06:50

MaxRecursion

4,773
12
42
76

14

Based on your statement: "regardless of the connection that created the value" - that sounds useless - if another connection inserted a row right after mine, I'd get his number - which I would later use to attempt to update the row I inserted, but instead, would update his. Right? – Traderhut Games May 24 '16 at 19:36
1

You use ‘ and ’ characters, which are not valid SQL. Instead, use ' character which *is* valid SQL. I have fixed this. – Ruudjah Feb 10 '20 at 15:10
6

How this got 49 upvotes is beyond me. `IDENT_CURRENT` is completely unreliable for determining the last row `*I*` inserted, but it's a great way to _maybe_ get the last row that I _or anyone else_ inserted. See [For the last time, NO, you can't trust IDENT_CURRENT()](https://sqlperformance.com/2014/01/t-sql-queries/ident-current) – Aaron Bertrand Sep 11 '20 at 14:46

Get the last inserted row ID (with SQL statement)

3 Answers3

Linked

Related