Using SQL's Scope_Identity

So you want to get the ID(PrimaryKey) of the last record inserted into the database without having to do a second cfquery block. Here is an example of doing it the hard way.


<cfset value1 = 2007>
<cfset myuuid = CreateUUID()>
<cfquery name="insert" datasource="#APPLICATION.DSN.DB#">
    INSERT INTO table(column1,column2)
    VALUES(#VARIABLES.value1#, '#VARIABLES.myUUID#')
</cfquery>

<cfquery name="select" datasource="#APPLICATION.DSN.DB#">
    SELECT ID
    FROM table
    WHERE column2=<cfqueryparam cfsqltype="cf_sql_varchar" value="#VARIABLES.myUUID#"/>
</cfquery>

You would then reference the ID as select.ID.

The easier way.


<cfquery name="insert" datasource="#APPLICATION.DSN.DB#">
    INSERT INTO table(column1,column2)
    VALUES(#VARIABLES.value1#, '#VARIABLES.myUUID#')
    SELECT Scope_Identity() AS ID
</cfquery>


The ID returned would be referenced as insert.ID ...less code, less work for coldfusion, less work for you.

After reading the comment from Ray Camden, I found out that a variable was added to the result attribute of the cfquery tag in ColdFusion 7. This would be the "IDENTITYCOL" variable and here is how you would use it.


<cfquery name="insert" datasource="#APPLICATION.DSN.DB#" result="myResult">
    INSERT INTO table(column1,column2)
    VALUES(#VARIABLES.value1#, '#VARIABLES.myUUID#')
</cfquery>


You would then reference the ID(PrimaryKey) as myResult.IDENTITYCOL(SQL Server Only)
-myResult.GENERATED_KEY for MySQL
-myResult.ROWID for Oracle
-myResult.SYB_IDENTITY for Sybase and
-myResult.SERIAL_COL for Informix

This approach is even faster,but you have to at least be on CFMX 7 to utilize it. Thanks for the reminder Ray!