RE: QofQ insert
Thanks for the replies. Yes, I was just tring not to do a possible 1k db calls. Just tring to cut down on the network traffic. I will do a few test runs and see if there is enough of a performance improvement to not do the loop. Thanks Rodney -Original Message- From: Brad Wood [mailto:[EMAIL PROTECTED] Sent: Monday, December 17, 2007 4:38 PM To: CF-Talk Subject: RE: QofQ insert I do not believe what you are looking for is possible. Depending on your DBMS you have a couple different options. Instead of creating a query object, perhaps you could re-factor your code to insert into a table variable in a cfquery tag and then to the insert from there. Another solution would be to serialize the query object into XML, and pass it into a proc or SQL statement where the XML could be parsed on the SQL server into a result set and do your insert from their. That would be pretty dependant on SQL Server 2000, or 2005 though. Why are you opposed to looping over the result set? If you are trying to avoid a performance problem, the methods above wouldn't be much better since they all involve looping at some level. You could at least try to minimalize locks open on the table by doing the insert all at once as opposed to one at a time. You could move the cfloop inside the cfquery and produce sql like such: Insert into table (col1, col2) (select 'foo1', 'bar1' Union all select 'foo2', 'bar2' Union all select 'foo3', 'bar3' Union all select 'foo'4, 'bar4') ~Brad -Original Message- ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:294991 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: QofQ insert
Depending on the DB you are using, you can qualify the table with the database name. I know this is possible in SQL Server. So you could write your query as: INSERT INTO DBName.table1 SELECT column1, column2, column3 FROM DBName.Table2Query Others do it a little differently using the in clause: INSERT INTO table1 in DBName SELECT column1, column2, column3 FROM Table2Query in DBName The IN clause may only be required to specify the foreign database if the code is being executed by one of the databases in question. Dave > -Original Message- > From: Bruce, Rodney Mr CTR USA AMC > [mailto:[EMAIL PROTECTED] > Sent: Monday, December 17, 2007 6:11 PM > To: CF-Talk > Subject: QofQ insert > > Hi all > > I have created a query, I can use dbtype="query". > I can do the loop: > > > INSERT INTO table1(column1, column2, > column3) > Values (Table2Query.column1,...) > > > > I am trying not to loop over my query for this insert. > > in SQL you can do: > INSERT INTO Table1 > SELECT column1, column2, column3 > FROM Table2 > > and insert records from table2 into table1 > > How can I do this with ? > > > INSER INTO table1 > SELECT column1, column2, column3 > FROM Table2Query > > > table1 needs the datasource > table2 is QoQ > > Is this possible? > > Thanks > Rodney > > ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:294970 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: QofQ insert
I do not believe what you are looking for is possible. Depending on your DBMS you have a couple different options. Instead of creating a query object, perhaps you could re-factor your code to insert into a table variable in a cfquery tag and then to the insert from there. Another solution would be to serialize the query object into XML, and pass it into a proc or SQL statement where the XML could be parsed on the SQL server into a result set and do your insert from their. That would be pretty dependant on SQL Server 2000, or 2005 though. Why are you opposed to looping over the result set? If you are trying to avoid a performance problem, the methods above wouldn't be much better since they all involve looping at some level. You could at least try to minimalize locks open on the table by doing the insert all at once as opposed to one at a time. You could move the cfloop inside the cfquery and produce sql like such: Insert into table (col1, col2) (select 'foo1', 'bar1' Union all select 'foo2', 'bar2' Union all select 'foo3', 'bar3' Union all select 'foo'4, 'bar4') ~Brad -Original Message- ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:294967 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
