Getting the auto generated id after insert in MSSQL
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>
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.
Add a comment
I am not sure, but it is unlikely.
<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
http://www.forta.com/blog/index.cfm/2007/7/6/ColdF...