Read data from one database and store it in other database

Question

I have a local database that contains 10 tables. My work is to read data from each table (select data from each table with some condition) one by one and then store that data in a remote database.

The remote database has the same 10 tables that are in the local database. In a simple way, I have to port data from the local database to the remote database.

How best to do this?

Answer 1

Make a stored procedure in the local database that does the copying from the local database to the remote database. Call that stored procedure from your Java program.

This is possible only if your database implementation supports linking to remote databases from a different implementation.

Answer 2

If i talk in oracle you can create a db link in the destination DB say source_link

http://docs.oracle.com/cd/B28359_01/server.111/b28310/ds_concepts002.htm

Then connect to the destination DB

insert into A (field1,field2,...) select field1,feild2.... from A@source_link where

Nothing to do with Java but a efficient way too