With thanks to some smart people on the CFTalk mailing list, here is a concise strategy for getting an automatically generated primary key id after inserting a record into an MSSQL database. Suppose you have a table Person with fields:
personId – int, auto Id, Primary Key
firstName – varchar
lastName – varchar
To insert a new record and retrieve the new record id you can use:
<cfquery name="personInsertQuery" datasource="mydsn"> set nocount on insert into Person ( firstName, lastName ) values ( <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#firstName#">, <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#lastName#"> ); set nocount off select scope_identity() as personId; </cfquery> <cfset personId = personInsertQuery.personId>
Easy.
6 Comments
That is fantastic!! How many years have I (and I’m sure others) been doing the old INSERT, then immediately SELECT Max(ID) AS NewID FROM xxxx routine to get the newly inserted ID.. I always thought CFQUERY should be able to return the new ID and hear it is. Brilliant. Thanks for posting this.
Does this work with mysql, too?
Hi Joern
I am not sure, but it is unlikely.
Hey, also note the new SQL Server 2005 feature – the OUTPUT clause:
<cfquery name="personInsertQuery" datasource="mydsn">
INSERT INTO Person(firstname,lastname)
OUTPUT
INSERTED.personID, INSERTED.firstname, INSERTED.lastname
VALUES (
<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#firstname#">,
<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#lastname#">
)
</cfquery>
Tony Brandner
Hi Tony, thanks for the update!
If you’re using CF8, this is built into <CFQUERY> now:
http://www.forta.com/blog/index.cfm/2007/7/6/ColdFusion-8-Can-Return-Identity-Values