scope_identity() is preferrable to @@identity. @@identity will return the latest ID generated by your statement, which means if your insert kicks off a trigger, for example, and the trigger also uses an ID value, you would get the ID from the trigger, not your main insert.
Scope_identity() is restricted to the scope of your main query. Now, chances are you don't have any triggers in play, but it's better to get in the habit of using scope_identity() so you don't have a surprise down the road. -----Original Message----- From: "Kevan Stannard" <[EMAIL PROTECTED]> To: "CF-Talk" <[email protected]> Sent: 1/17/07 8:58 PM Subject: Getting auto generated id after insert in MSSQL Hi everyone Can anyone advise if this is the best way to get the auto generated id from MSSQL after an insert? Is it multiuser safe? <cfquery name="personInsertQuery" datasource="mydb" > insert into person (firstName,lastName) values ( <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#firstName#">, <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#lastName#"> ); select @@identity as personId; </cfquery> <cfset personId = personInsertQuery.personId> Thanks Kevan ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Upgrade to Adobe ColdFusion MX7 Experience Flex 2 & MX7 integration & create powerful cross-platform RIAs http:http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:266845 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
