INSERT INTO get primary key ID from current

Question

How can I return the primary key ID value from my most recent query when working in ColdFusion?

For example:

<cffunction name="addAction" access="public" returntype="string">
  <cfquery name="newAction" datasource="RC">

    INSERT INTO myTable (name,address,title) VALUE (#fname#,#address#,#usrtitle#);

  </cfquery>

  <cfset actionUpdateID = #result_name.IDENTITYCOL#>

  <cfreturn actionUpdate>
</cffunction>

In the example above I am trying to get actionUpdate to return the value of the primary key which is a column named ID.

Answer 1

I wrote "Please stop using SELECT MAX(id)" to cover exactly this issue. Every database has a built-in function that will return the primary key of a newly inserted record on a table.

In SQL Server, SCOPE_IDENTITY() returns the ID of the last record that was created within the current scope, regardless of the table.

So your function should look more like this:

1. You should be passing in values as arguments of the function (including data type and required values).
2. Your values should be using CFQUERYPARAM to protect from SQL Injection attacks.
3. Your function should include output="false".
4. Your function's return type should be numeric since you're returning an integer value (the new primary key).

<cffunction name="addAction" access="public" output="false" returntype="numeric">
    <cfargument name="fname" type="string" required="true">
    <cfargument name="address" type="string" required="true">
    <cfargument name="usrtitle" type="string" required="true">
    <cfquery name="newAction" datasource="RC">
        INSERT INTO myTable (
            name,
            address,
            title
        ) 
        VALUE (
            <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.fname#">,
            <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.address#">,
            <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.usrtitle#">
        );
        // Return the new primary key for this record.
        SELECT SCOPE_IDENTITY() AS NEW_ID;
    </cfquery>
    <cfreturn newAction.NEW_ID >
</cffuntion>

FWIW, MySQL has the function LAST_INSERT_ID() that does the same thing. You will have to update a setting in your data source in CF Admin to allow MySQL to run multiple statements.

Answer 2

If you're working in a reasonably recent version of Microsoft SQL Server, you can use OUTPUT INSERTED.columnname with your identity column, like this:

<cfquery name="newAction" datasource="RC">
INSERT INTO myTable 
(
  name,
  address,
  title
) 
OUTPUT INSERTED.ID
VALUES 
(
  #fname#,
  #address#,
  #usrtitle#
)
</cfquery>

<cfset actionUpdateID = newAction.person_id>

The example assumes that ID is your identity column.

(It also shouldn't need to be said that you should be using <cfqueryparam ...>.)

---

Alternatively, assuming you're using a reasonably recent version of ColdFusion, the metadata returned by CFQUERY includes the identity on insert, so long as you define a name for the result="" attribute. If you used myResult for the name of the result variable, you can use (depending on your flavor of database):

myResult.IDENTITYCOL    SQL Server only. The ID of an inserted row.

myResult.ROWID          Oracle only. The ID of an inserted row. This is not
                        the primary key of the row, although you can retrieve
                        rows based on this ID.

myResult.SYB_IDENTITY   Sybase only. The ID of an inserted row.

myResult.SERIAL_COL     Informix only. The ID of an inserted row.

myResult.GENERATED_KEY  MySQL only. The ID of an inserted row. MySQL 3 
                        does not support this feature.

myResult.GENERATEDKEY   Supports all databases. The ID of an inserted row.

(ColdFusion documentation)

So the example above would look like this:

<cfquery name="newAction" datasource="RC" result="queryResults">
INSERT INTO myTable 
(
  name,
  address,
  title
) 
VALUES 
(
  #fname#,
  #address#,
  #usrtitle#
)
</cfquery>

<cfset actionUpdateID = queryResults.IDENTITYCOL>