Retrieve inserted row ID in SQL

How d开发者_如何学Goo I retrieve the ID of an inserted row in SQL?

Users Table:

Column  | Type
--------|--------------------------------
ID      | * Auto-incrementing primary key
Name    | 
Age     | 

Query Sample:

insert into users (Name, Age) values ('charuka',12)

---

In MySQL:

SELECT LAST_INSERT_ID();

In SQL Server:

SELECT SCOPE_IDENTITY();

In Oracle:

SELECT SEQNAME.CURRVAL FROM DUAL;

In PostgreSQL:

SELECT lastval();

(edited: lastval is any, currval requires a named sequence) Note: lastval() returns the latest sequence value assigned by your session, independently of what is happening in other sessions.

---

In SQL Server, you can do (in addition to the other solutions already present):

INSERT INTO dbo.Users(Name, Age) 
OUTPUT INSERTED.ID AS 'New User ID'
VALUES('charuka', 12)

The OUTPUT clause is very handy when doing inserts, updates, deletes, and you can return any of the columns - not just the auto-incremented ID column.

Read more about the OUTPUT clause in the SQL Server Books Online.

---

In Oracle and PostgreSQL you can do this:

INSERT INTO some_table (name, age)
VALUES
('charuka', 12)
RETURNING ID

When doing this through JDBC you can also do that in a cross-DBMS manner (without the need for RETURNING) by calling getGeneratedKeys() after running the INSERT

---

I had the same need and found this answer ..

This creates a record in the company table (comp), it the grabs the auto ID created on the company table and drops that into a Staff table (staff) so the 2 tables can be linked, MANY staff to ONE company. It works on my SQL 2008 DB, should work on SQL 2005 and above.

===========================

CREATE PROCEDURE [dbo].[InsertNewCompanyAndStaffDetails]

 @comp_name varchar(55) = 'Big Company',

 @comp_regno nchar(8) = '12345678',

 @comp_email nvarchar(50) = 'no1@home.com',

 @recID INT OUTPUT

-- The '@recID' is used to hold the Company auto generated ID number that we are about to grab

AS
 Begin

  SET NOCOUNT ON

  DECLARE @tableVar TABLE (tempID INT)

-- The line above is used to create a tempory table to hold the auto generated ID number for later use. It has only one field 'tempID' and its type INT is the same as the '@recID'.

  INSERT INTO comp(comp_name, comp_regno, comp_email) 

  OUTPUT inserted.comp_id INTO @tableVar

-- The 'OUTPUT inserted.' line above is used to grab data out of any field in the record it is creating right now. This data we want is the ID autonumber. So make sure it says the correct field name for your table, mine is 'comp_id'. This is then dropped into the tempory table we created earlier.

  VALUES (@comp_name, @comp_regno, @comp_email)

  SET @recID = (SELECT tempID FROM @tableVar)

-- The line above is used to search the tempory table we created earlier where the ID we need is saved. Since there is only one record in this tempory table, and only one field, it will only select the ID number you need and drop it into '@recID'. '@recID' now has the ID number you want and you can use it how you want like i have used it below.

  INSERT INTO staff(Staff_comp_id) 
  VALUES (@recID)

 End

-- So there you go. I was looking for something like this for ages, with this detailed break down, I hope this helps.