plus one for David's explanation re: new ID...nail hit on head ;-) On Tue, 2010-10-26 at 15:35 -0400, David McGraw wrote:
> 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:338588 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
