[sql] Get the last inserted row ID (with SQL statement)

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 ?

This question is related to sql sql-server

The answer is


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.


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.