Problem

After inserting a record into a database the application needs the id of the record that was just inserted.

Solution

ColdFusion provides wealth of information in the result variable of a <cfquery> tag.

Detailed explanation

Often times an application needs to have the id of a record just interted into a database.  ColdFusion provides an easy way to get this id as well as a host of other information by specifying a variable name in the result attribute of the <cfquery> tag.  The variable that is returned is a structure. The result attributes is available in ColdFusion 7, however the information returned about the id is only available in ColdFusion 8 and later.

The following <cfquery> code specifies the result attribute:

<cfquery datasource="#dsn#" result="stResult">
INSERT INTO
tbl_Person
(
PersonName,
PersonEmail
)

VALUES
(
<cfqueryparam value="#PersonName#" cfsqltype="cf_sql_varchar"
/>,
<cfqueryparam value="#PersonEmail#" cfsqltype="cf_sql_varchar"
/>
)
</cfquery>

After the query executes the stResult variable will hold details about the query.   The general information keys are sql, recordcount, cached, sqlparameters, columnList and ExecutionTime.  The are available in ColdFusion 7.  With ColdFusion 8 additional fields were added to return the id of the last inserted record.  Depending on what database the application is utilizing the database specific field will be populated.

IDENTITYCOL – SQL Server

ROWID – Oracle (This is the ROWID not the primary key. )

SYB_IDENTITY - Sybase

SERIAL_COL – Informix

GENERATED_KEY – MySQL

The following code will output the ID of the last inserted record assuming the application is using SQL Server.

<cfoutput>
#stResult['IDENTITYCOL']#
</cfoutput>

A very simple and quick way to get the id of the last inserted record provided by ColdFusion

source: Coldfusion Cookbook