Re: Fastest way to copy table between databases

[Phillip Ward]

SQL statements can only be executed through one connection.

Databases are separate entities.
If you don't provide a "tunnel" (I'm avoiding the word "connection" here) between one database and the other then you simply cannot do this job at the SQL level.

SQL statements execute in the [security] context of the username specified in the connection.
Even if you do provide a "tunnel" between the databases, the account must have access to use that "tunnel".

As a one-off solution, the fastest alternative would be to "export" (dump) the data from one database and "import" it into the other.
Of course, the table structures would have to be identical in both databases and, assuming you're using Transactions, this process might well be "all or nothing"; you'll get all the data loaded or an error and it all gets rolled back and thrown away.

If you need to do this regularly, you might consider automating this export/import processing.
Alternatively, you might write a custom program that reads data (row-by-row) from one connection and writes it to the other. This will, of course, will be slower overall, but you'll have a greater degree of control over the processing).
YMMV.

Regards, Phill W.