Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Discussion" <derby-user@db.apache.org>
Received-SPF: pass (athena.apache.org: domain of bpendleton.derby@gmail.com
 designates 209.85.160.44 as permitted sender)
DomainKey-Signature: a=rsa-sha1; c=nofws;
        d=gmail.com; s=gamma;
        h=message-id:date:from:user-agent:mime-version:to:subject:references
         :in-reply-to:content-type:content-transfer-encoding;
        b=LTbHpmL1cBBGl8DZ0NKI0lcN5GjVPHJolwnexDvY//Z+LMXBNwIwqs9yznAM/jDeVn
         F0jksVwds4b632ymZz6QBTEqZt8H7WE/4P0HvsSAq6+bsuWG0guug6mY02r17xakE/mr
         6jEiTnyUiBMDaK1f1A/FbsvYKTGl8MHt9yMXw=
Message-ID: <4BD453C7.2040201@gmail.com>
Date: Sun, 25 Apr 2010 07:37:59 -0700
From: Bryan Pendleton <bpendleton.derby@gmail.com>
User-Agent: Thunderbird 2.0.0.24 (X11/20100411)
MIME-Version: 1.0
To: Derby Discussion <derby-user@db.apache.org>
Subject: Re: Transfer data between tables that are located in different
 databases
References: <210082.32277.qm@web59503.mail.ac4.yahoo.com>
In-Reply-To: <210082.32277.qm@web59503.mail.ac4.yahoo.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

> 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