Retrieve inserted row ID in SQL

Question

How do 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)

Answer 1

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.

Answer 2

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.