Transfer data between tables that are located in different databases

[Dinesh Bajaj <di...@ymail.com>]

Hi,

Could someone give me pointers on how to transfer data between two tables that are identical in structure, but located in different databases. This task needs to be accomplished through code.

Thanks in advance.

-Dinesh

Re: Transfer data between tables that are located in different databases

[Dinesh Bajaj <di...@ymail.com>]

Bryan,

You are correct as I wanted the source and target tables to be in different databases, and so the Chris's solution would have not worked for me.

BTW, I used system procedures to export the data from the source table to a file, and then imported the data from that file to the target table to get the required job done.

The proposed new feature that you referenced will be a welcome addition to the command set of Derby. :)

-Dinesh


--- On Mon, 26/4/10, Bryan Pendleton <bp...@gmail.com> wrote:

From: Bryan Pendleton <bp...@gmail.com>
Subject: Re: Transfer data between tables that are located in different databases
To: "Derby Discussion" <de...@db.apache.org>
Date: Monday, 26 April, 2010, 7:40 PM

>> As it is a lot of data that needs to be transferred between tables, the idea of using the system procedures to export the data from the source table, and then importing it in the target table appears to be good one. I will definitely give it a try, and will post the code here if I encounter any error.
> 
> How about in SQL:
> 
> INSERT INTO target SELECT * FROM source;

The tricky part is that the original poster wanted the source and target
tables to be in separate databases, which makes this solution impractical.

However, Derby 10.6's ij tool will contain a new feature DERBY-4550
https://issues.apache.org/jira/browse/DERBY-4550
which will enable uses such as this in the ij tool, I believe.

thanks,

bryan

Re: Transfer data between tables that are located in different databases

[Bryan Pendleton <bp...@gmail.com>]

>> As it is a lot of data that needs to be transferred between tables, the 
>> idea of using the system procedures to export the data from the source 
>> table, and then importing it in the target table appears to be good one. 
>> I will definitely give it a try, and will post the code here if I 
>> encounter any error.
> 
> How about in SQL:
> 
> INSERT INTO target SELECT * FROM source;

The tricky part is that the original poster wanted the source and target
tables to be in separate databases, which makes this solution impractical.

However, Derby 10.6's ij tool will contain a new feature DERBY-4550
https://issues.apache.org/jira/browse/DERBY-4550
which will enable uses such as this in the ij tool, I believe.

thanks,

bryan

Re: Transfer data between tables that are located in different databases

[Chris Wilson <ch...@aptivate.org>]

Hi Dinesh,

On Sun, 25 Apr 2010, Dinesh Bajaj wrote:

> I haven't written any code yet to implement this functionality, as I 
> couldn't thought of a decent way to do this. I was mainly looking for a 
> feature to initialize the target ResultSet from the source ResultSet and 
> then updating the target one to save the data in one go. I wanted to 
> avoid the mundane task of reading row-by-row from a source Resultset and 
> then putting in the target ResultSet.
> 
> As it is a lot of data that needs to be transferred between tables, the 
> idea of using the system procedures to export the data from the source 
> table, and then importing it in the target table appears to be good one. 
> I will definitely give it a try, and will post the code here if I 
> encounter any error.

How about in SQL:

INSERT INTO target SELECT * FROM source;

Without any resultsets.

Cheers, Chris.
-- 
Aptivate | http://www.aptivate.org | Phone: +44 1223 760887
The Humanitarian Centre, Fenner's, Gresham Road, Cambridge CB1 2ES

Aptivate is a not-for-profit company registered in England and Wales
with company number 04980791.

Re: Transfer data between tables that are located in different databases

[Dinesh Bajaj <di...@ymail.com>]

Hi Bryan,

Thanks for your response.

I haven't written any code yet to implement this functionality, as I couldn't thought of a decent way to do this. I was mainly looking for a feature to initialize the target ResultSet from the source ResultSet and then updating the target one to save the data in one go. I wanted to avoid the mundane task of reading row-by-row from a source Resultset and then putting in the target ResultSet.

As it is a lot of data that needs to be transferred between tables, the idea of using the system procedures to export the data from the source table, and then importing it in the target table appears to be good one. I will definitely give it a try, and will post the code here if I encounter any error.

Thanks again for your reply.

-Dinesh

--- On Sun, 25/4/10, Bryan Pendleton <bp...@gmail.com> wrote:

From: Bryan Pendleton <bp...@gmail.com>
Subject: Re: Transfer data between tables that are located in different databases
To: "Derby Discussion" <de...@db.apache.org>
Date: Sunday, 25 April, 2010, 8:07 PM

> Could someone give me pointers on how to transfer data between two tables that are identical in structure, but located in different databases. This task needs to be accomplished through code.

A single program can certainly have multiple databases open. Each
java.sql.Connection object is tied to a single database, but you can
have several Connection objects, each pointing to a different database,
and switch back and forth between them in your program as you need to.

If it's a lot of data, you could run the export/import system procedures.
Export the data from the source table to a text file, then import it
to the target table.

If it's a small amount of data, you can open two connections in your
program, read the data from the source table, and insert the data
into the target table.

Depending on how generic your transfer code needs to be, you could
simply hard-code the names of columns and their data types in your code.
Or, you could use the DatabaseMetaData classes to determine the
column names at runtime and write a generic transfer routine.

Why don't you post the code you've tried so far, and the problems
you've encountered, and the community can then suggest solutions
to those problems.

thanks,

bryan

Re: Transfer data between tables that are located in different databases

[Bryan Pendleton <bp...@gmail.com>]

> Could someone give me pointers on how to transfer data between two 
> tables that are identical in structure, but located in different 
> databases. This task needs to be accomplished through code.

A single program can certainly have multiple databases open. Each
java.sql.Connection object is tied to a single database, but you can
have several Connection objects, each pointing to a different database,
and switch back and forth between them in your program as you need to.

If it's a lot of data, you could run the export/import system procedures.
Export the data from the source table to a text file, then import it
to the target table.

If it's a small amount of data, you can open two connections in your
program, read the data from the source table, and insert the data
into the target table.

Depending on how generic your transfer code needs to be, you could
simply hard-code the names of columns and their data types in your code.
Or, you could use the DatabaseMetaData classes to determine the
column names at runtime and write a generic transfer routine.

Why don't you post the code you've tried so far, and the problems
you've encountered, and the community can then suggest solutions
to those problems.

thanks,

bryan