Indeed, this is clearly identified within BOL. In most cases, where you know the schema etc then @@identity will work but to be safe it is best to use one of the scoped returns.
"This e-mail is from Reed Exhibitions (Gateway House, 28 The Quadrant, Richmond, Surrey, TW9 1DN, United Kingdom), a division of Reed Business, Registered in England, Number 678540. It contains information which is confidential and may also be privileged. It is for the exclusive use of the intended recipient(s). If you are not the intended recipient(s) please note that any form of distribution, copying or use of this communication or the information in it is strictly prohibited and may be unlawful. If you have received this communication in error please return it to the sender or call our switchboard on +44 (0) 20 89107910. The opinions expressed within this communication are not necessarily those expressed by Reed Exhibitions." Visit our website at http://www.reedexpo.com -----Original Message----- From: Doug Bezona To: CF-Talk Sent: Thu Jan 18 03:27:48 2007 Subject: RE: Getting auto generated id after insert in MSSQL 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:266849 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
