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!

ColdFusion Ninja for hire.

I do use this syntax when all tables involved are in the same database.  However, in this case the tables I am querying from are in database A (postgresql 8.4) on server A and the table I need to insert into is in database B (postgresql 7.4) on server B.
Thanks.

oops. why not loop over the query and do your inserts?

 

ColdFusion Ninja for hire.

Yes, that is what I am doing, but it is very slow.  I was wondering if anyone had a better idea for a faster way.