Probably becuase when using a SELECT INTO you can potential insert more than just 1 record, so that probably doesn't work with that. What does TMP look liek when you just dump that after the query? Maybe it's an array of IDs? Doubt it, but that's what I would assume since it could in theory return multiple "New Ids"
On Tue, Oct 26, 2010 at 3:30 PM, Matt Quackenbush <[email protected]>wrote: > > Thanks. As I already posted, it's a bug in CF8. For some reason, CF8 does > not properly handle an SELECT INTO insert, and does not return the new ID. > So the workaround is to use a standard VALUES insert instead. > > Now then, if you do not need to know the newly inserted ID, then CF8 > handles > the SELECT INTO just fine. > > <!--- this works just fine in CF8 ---> > <cfquery> > INSERT INTO > myTable > SELECT > 'foo', > myOtherTable.IDcol > FROM > myOtherTable > WHERE > myOtherTable.bar = 'wtf?' > ; > </cfquery> > > However, this fails when you try and reference the query/new ID. > > <cfquery name="tmp"> > INSERT INTO > myTable > SELECT > 'foo', > myOtherTable.IDcol > FROM > myOtherTable > WHERE > myOtherTable.bar = 'wtf?' > ; > SELECT > scope_identity() AS NewId > FROM > myTable > ; > </cfquery> > <cfset theNewId = tmp.NewId[1] /> <!--- this is where it fails ---> > > Even if you use the 'result' attribute instead of the 'name' attribute, the > query will do the insert, but the 'identitycol' key is undefined in the > 'result' struct. > > <cfquery result="tmp"> > INSERT INTO > myTable > SELECT > 'foo', > myOtherTable.IDcol > FROM > myOtherTable > WHERE > myOtherTable.bar = 'wtf?' > ; > </cfquery> > <cfset theNewId = tmp.identitycol /> <!--- this is where it fails ---> > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:338582 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
