I guess I am so lost, I thought Matt just said what I suggested would generate an error?
On Tue, Oct 26, 2010 at 3:42 PM, Bryan Stevenson < [email protected]> wrote: > > 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:338589 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
