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

User · Answer

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

User · Answer

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

User · Answer

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

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

Examples related to sql

Examples related to sql-server