Retrieving Last Inserted ID

A common functionality many developers need at one time or another is to get the ID of the row they just inserted into a database. When the database's primary key is a number automatically generated by the database, it can be difficult.

Different database packages use different methods.

Microsoft Access
With Access's AutoNumber columns, the only method to use is to use two queries, one to insert the row and another to pull the maximum value of the primary key in that table. Both queries should be within a tag to ensure the returned value belongs to the row just inserted:




INSERT INTO Table ...
valueS ...


SELECT MAX(ID_Field) AS ThisID
FROM Table





SQL Server
If you are using SQL Server and its Identity column, you can perform the insert and ID retrieval in just one query:



SET NOCOUNT ON
INSERT INTO Table ...
valueS ...
SELECT ThisID = @@Identity
SET NOCOUNT OFF




NOCOUNT is set to ON to reduce network traffic during the multiple queries, then set back to OFF when complete. @@Identity contains the value of the last inserted ID.

Oracle
Oracle doesn't have an AutoNumber or Identity column like the Microsoft databases and instead uses Sequences. A sequence must first be created for a table before it can be accessed. After you create the sequence, you'll need a query to obtain the next value before the actual insert. You can use that value in the insert itself. So unlike Access and SQL Server, you must specify the value of the ID in the insert query.




SELECT Table_Sequence.NextVal AS ThisID
FROM Dual



INSERT INTO Table (ID, ...)
valueS (#LastID#, ...)