That would ONLY work if you used SET TRANSACTION ISOLATION LEVEL SERIALIZABLE or WITH(TABLOCKX HOLDLOCK)
Even with with rowlock, another spid can insert additional records into the table and the select max() might return a bogus value. ~Brad -------- Original Message -------- Subject: Re: SQL Gurus... obtaining the correct Incremental ID From: nvc 1 <[email protected]> Date: Wed, April 28, 2010 3:14 pm To: cf-talk <[email protected]> why can't you do this where mytable has an auto incrementing primary key called id: <cftransaction> <cfquery datasource="#dsn#" name="insertQuery"> insert into mytable(acolumn) values(1) </cfquery> <cfquery datasource="#dsn#" name="getid"> select max(id) as maxid from mytable </cfquery> </cftransaction> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:333198 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
