populate table from query results

[jdavenport]

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!

 

[FALCONSEYE]

INSERT into myTable (myID, field1, field2)
SELECT otherID, otherField1, otherField2
FROM myOtherTable
WHERE something = something

ColdFusion Ninja for hire.

 

[jdavenport]

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.

 

[FALCONSEYE]

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

<cfoutput query="qry1">
	<cfquery datasource="#dsn#">
    	insert into myTable (field1, field2, field3)
        values ( <cfqueryparam cfsqltype="cf_sql_varchar" value="#field1#">,
        		 <cfqueryparam cfsqltype="cf_sql_varchar" value="#field2#">,
                 <cfqueryparam cfsqltype="cf_sql_varchar" value="#field3#"> )
    </cfquery>
</cfoutput>

ColdFusion Ninja for hire.

 

[jdavenport]

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.