One way is to use two DSNs, and create the INSERT once:
<cfquery name="q1" datasource="DSN1">
select col1, col2 from table1
</>
<cfsavecontent variable="q">
INSERT INTO table2 (col1, col2) VALUES
<cfloop query="q1">
(#q1.col1#, '#q1.col2#')
<cfif q1.currentRow EQ q1.recordCount>;<cfelse>,</cfif>
</>
</>
<cfquery name="q2" datasource="DSN2">
#q#
</>
Something like that, HTH.
- Rex
On 7/7/2011 11:21 AM, Julie Davenport wrote:
> What is the fastest way to populate a table with the results of a query? I
> have a join query over 2 tables in database A and I need to insert all the
> rows found into a table in database B.
>
> I cannot do it all in one query:
> insert into table3 (select ... from table1, table2...);
> because the tables are in different databases and postgresql does not allow
> inter-db queries (and the cfquery datasource can only point to one database
> at a time anyway).
>
> I'd like to use Coldfusion Query of Query to help but don't see how. I
> cannot query from the query results and insert into a table all in the same
> cfquery (since a cfquery tag can have EITHER the datasource pointing to
> database table is in, OR dbtype=query to do QofQ):
> insert into table3 (select * from myQuery);
>
> As a result, I have to do the query, then loop through the query results, and
> each time through the loop, I insert a row of the results into the table.
> This is very slow! Is there a faster way with CF?
>
> Thanks!
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
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:346135
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
