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 <CFTRANSACTION> tag to ensure the returned value belongs to the row just inserted:

<cftransaction>
   <cfquery datasource="AccessDS">
   INSERT INTO Table ...
   VALUES ...
   </cfquery>
   <cfquery datasource="AccessDS" name="GetID">
   SELECT MAX(ID_Field) AS ThisID
     FROM Table
   </cfquery>
   <cfset LastID = GetID.ThisID>
</cftransaction>


SQL Server

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

   <cfquery datasource="SQLServerDS" name="InsertQuery">
      SET NOCOUNT ON
   INSERT INTO Table ...
   VALUES ...
   SELECT ThisID = @@Identity
      SET NOCOUNT OFF
   </cfquery>
   <cfset LastID = InsertQuery.ThisID>

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.

<cftransaction>
   <cfquery datasource="OracleDS" name="GetID">
   SELECT Table_Sequence.NextVal AS ThisID
     FROM Dual
   </cfquery>
   <cfset LastID = GetID.ThisID>
   <cfquery datasource="OracleDS">
   INSERT INTO Table (ID, ...)
   VALUES (#LastID#, ...)
   </cfquery>
</cftransaction>

Join Our E-Mail Newsletter

For Email Newsletters you can trust


Sister Sites

RiteTech LLC
Modern HOA