How to get ID (PK) of newly created record?

Question

Given a table:

CREATE TABLE [GENERIC_TABLE] (
  [RECORD_ID] [int] IDENTITY(1,1) NOT NULL,
  [SHORT_DESC] [varchar] (50) NULL,
 CONSTRAINT [PK_GENERIC_TABLE] PRIMARY KEY CLUSTERED
 ...

I want to INSERT a record and get the value of the new RECORD_ID into a ColdFusion variable.

What should my CFQUERY look like?

(Admittedly, this is probably an overly easy question. In my defense I am used to working with Oracle, not SQL Server.)

This for ColdFusion 8, but version-neutral solutions are good.

Answer 1

If you set the result attribute in cfquery tag the primary key is returned without extra SQL

<cfquery datasource="x" result="resultName">
INSERT INTO...
</cfquery>

<cfset newID = resultName.IDENTITYCOL />

• MSSQL: IDENTITYCOL
• Oracle: ROWID
• Sybase: SYB_IDENTITY
• Informix: SERIAL_COL
• Mysql: GENERATED_KEY

Answer 2

For some reason you can't use the result when you are duplicating a row. Here is the solution I found for that case. (Note, the syntax SQL Server specific)

<cfquery name="dupRecord" datasource="#application.dsn#" result="dupResult">
    SET NOCOUNT ON;

    INSERT INTO dataProjects (DraftId,PublishStatus,ProjectName)
    SELECT 0,'draft',ProjectName
    FROM dataProjects
    WHERE projectId = <cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#arguments.projectId#">;

    SELECT SCOPE_IDENTITY() AS RecordID;
</cfquery>
<cfset dupProjectId = dupRecord.RecordID>