populate table from query results

[jdavenport11]

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 like this:

insert into table3 (select * from myQuery);

since a cfquery tag can have EITHER the datasource pointing to the database that the table is in, OR dbtype=query to do QofQ.

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! 

[Dan_Bracuk]

It might be faster to put your loop inside your query instead of the other way around.  Try it and see.

[jdavenport11]

Interesting!  I will try it and let you know.

thanks.

[Adam Cameron.]

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. 

If poss, grant perms on one of the DBs to the other DB, and copy the data via the DB.

Don't involve CF at all.  CF is not a data interchange tool.

--

Adam