JDBC insert into select between two databases(local and remote)

JDBC insert into select between two databases(local and remote),jdbc,Jdbc,There are two databases, the local one (damn MYOB data file, can only read locally via JDBC-ODBC connection), the other one is mysql which runs on another server. what is the best way to synchronize the two databases? is it possible to execute "insert into _mysql.table1 select * from _myob.table1" or do something similar? I hate to select from, then populate the result insert into another. Thanks.

There are two databases, the local one (damn MYOB data file, can only read locally via JDBC-ODBC connection), the other one is mysql which runs on another server.

what is the best way to synchronize the two databases?

is it possible to execute "insert into _mysql.table1 select * from _myob.table1" or do something similar?

I hate to select from, then populate the result insert into another.

Thanks.


#1

That's not possible, you can create a statement from 2 database connections. You can try SQuirreL SQL, which has a DB Copy plugin.


#2

I am not sure if your requirement is one off or it needs to be periodic.

If its one time, you can bring the data into Excel from odbc, save as csv, and on mysql use the LOAD command (http://dev.mysql.com/doc/refman/5.0/en/load-data.html).

If its periodic, you have to have two connections and run a select query on the local one, bring the resultsets into your java program and run insert on the remote connection.