You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by jaz1988 <88...@gmail.com> on 2012/06/15 08:26:47 UTC
Importing tables which have references to other tables
Hi,
I have a table, TABLE1 which has a column which is GENERATED BY DEFAULT AS
IDENTITY. I managed to export this table and import into an existing DB with
the same table. The import is able to adjust the IDENTITY column accordingly
with regards to the existing data in the table.
The problem is, I have another table, TABLE2 which has a foreign key that
referenced to the IDENTITY column in TABLE1. When the IDENTITY column in
TABLE1 is adjusted after import, the import of TABLE2 will fail since the
References is messed up already. Am I doing it right or is there other ways
to do import and exports of Tables with references.
Thank you!
--
View this message in context: http://old.nabble.com/Importing-tables-which-have-references-to-other-tables-tp34016274p34016274.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.
RE: Importing tables which have references to other tables
Posted by jaz1988 <88...@gmail.com>.
Hi im currently using some derby export methods to export table by table to a
.del type of file. After which i will import the .del files using the import
methods. You can take a look here:
http://db.apache.org/derby/docs/10.6/tools/ctoolsimport16245.html
Bartosz, Jan (NSN - PL/Wroclaw) wrote:
>
> HI,
>
> I don't have answer to your question... however I'm just curious how
> your export & import process looks like? - Export-> file and then from
> file-> DB ? is there a way to export&import at same time from DB1->DB2
> let's say I want to make a copy from a subset of DB1 and copy it to DB2
> (without statement&ResultSet just by using some tools from DERBY).
> Many Thanks for any help;)!
> BR
> Jan
>
> ..........................
> Regards / Pozdrowienia
> Jan Bartosz
> MBB RP I&V Tools&Autom Pegasus Core PL
> My contact info in Phonebook
> Click to call me by using Softphone
>
> -----Original Message-----
> From: ext jaz1988 [mailto:88nosaj@gmail.com]
> Sent: Friday, June 15, 2012 8:27 AM
> To: derby-user@db.apache.org
> Subject: Importing tables which have references to other tables
>
>
> Hi,
>
> I have a table, TABLE1 which has a column which is GENERATED BY DEFAULT
> AS
> IDENTITY. I managed to export this table and import into an existing DB
> with
> the same table. The import is able to adjust the IDENTITY column
> accordingly
> with regards to the existing data in the table.
>
> The problem is, I have another table, TABLE2 which has a foreign key
> that
> referenced to the IDENTITY column in TABLE1. When the IDENTITY column in
> TABLE1 is adjusted after import, the import of TABLE2 will fail since
> the
> References is messed up already. Am I doing it right or is there other
> ways
> to do import and exports of Tables with references.
>
> Thank you!
> --
> View this message in context:
> http://old.nabble.com/Importing-tables-which-have-references-to-other-ta
> bles-tp34016274p34016274.html
> Sent from the Apache Derby Users mailing list archive at Nabble.com.
>
>
>
--
View this message in context: http://old.nabble.com/Importing-tables-which-have-references-to-other-tables-tp34016274p34016712.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.
Re: Importing tables which have references to other tables
Posted by Rick Hillegas <ri...@oracle.com>.
On 6/14/12 11:37 PM, Bartosz, Jan (NSN - PL/Wroclaw) wrote:
> HI,
>
> I don't have answer to your question... however I'm just curious how
> your export& import process looks like? - Export-> file and then from
> file-> DB ? is there a way to export&import at same time from DB1->DB2
> let's say I want to make a copy from a subset of DB1 and copy it to DB2
> (without statement&ResultSet just by using some tools from DERBY).
> Many Thanks for any help;)!
> BR
> Jan
Hi Jan,
You might want to take a look at ForeignTableVTI, attached to
https://issues.apache.org/jira/browse/DERBY-4962. Using that tool, you
should be able to import directly from another database without having
to indirect through a dump file.
Hope this helps,
-Rick
> ..........................
> Regards / Pozdrowienia
> Jan Bartosz
> MBB RP I&V Tools&Autom Pegasus Core PL
> My contact info in Phonebook
> Click to call me by using Softphone
>
> -----Original Message-----
> From: ext jaz1988 [mailto:88nosaj@gmail.com]
> Sent: Friday, June 15, 2012 8:27 AM
> To: derby-user@db.apache.org
> Subject: Importing tables which have references to other tables
>
>
> Hi,
>
> I have a table, TABLE1 which has a column which is GENERATED BY DEFAULT
> AS
> IDENTITY. I managed to export this table and import into an existing DB
> with
> the same table. The import is able to adjust the IDENTITY column
> accordingly
> with regards to the existing data in the table.
>
> The problem is, I have another table, TABLE2 which has a foreign key
> that
> referenced to the IDENTITY column in TABLE1. When the IDENTITY column in
> TABLE1 is adjusted after import, the import of TABLE2 will fail since
> the
> References is messed up already. Am I doing it right or is there other
> ways
> to do import and exports of Tables with references.
>
> Thank you!
RE: Importing tables which have references to other tables
Posted by "Bartosz, Jan (NSN - PL/Wroclaw)" <ja...@nsn.com>.
HI,
I don't have answer to your question... however I'm just curious how
your export & import process looks like? - Export-> file and then from
file-> DB ? is there a way to export&import at same time from DB1->DB2
let's say I want to make a copy from a subset of DB1 and copy it to DB2
(without statement&ResultSet just by using some tools from DERBY).
Many Thanks for any help;)!
BR
Jan
..........................
Regards / Pozdrowienia
Jan Bartosz
MBB RP I&V Tools&Autom Pegasus Core PL
My contact info in Phonebook
Click to call me by using Softphone
-----Original Message-----
From: ext jaz1988 [mailto:88nosaj@gmail.com]
Sent: Friday, June 15, 2012 8:27 AM
To: derby-user@db.apache.org
Subject: Importing tables which have references to other tables
Hi,
I have a table, TABLE1 which has a column which is GENERATED BY DEFAULT
AS
IDENTITY. I managed to export this table and import into an existing DB
with
the same table. The import is able to adjust the IDENTITY column
accordingly
with regards to the existing data in the table.
The problem is, I have another table, TABLE2 which has a foreign key
that
referenced to the IDENTITY column in TABLE1. When the IDENTITY column in
TABLE1 is adjusted after import, the import of TABLE2 will fail since
the
References is messed up already. Am I doing it right or is there other
ways
to do import and exports of Tables with references.
Thank you!
--
View this message in context:
http://old.nabble.com/Importing-tables-which-have-references-to-other-ta
bles-tp34016274p34016274.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.
Re: Importing tables which have references to other tables
Posted by Tim Watts <ti...@cliftonfarm.org>.
On Fri, 2012-06-15 at 08:18 -0400, Tim Watts wrote:
> On Thu, 2012-06-14 at 23:26 -0700, jaz1988 wrote:
> > Hi,
> >
> > I have a table, TABLE1 which has a column which is GENERATED BY DEFAULT AS
> > IDENTITY. I managed to export this table and import into an existing DB with
> > the same table. The import is able to adjust the IDENTITY column accordingly
> > with regards to the existing data in the table.
> >
> > The problem is, I have another table, TABLE2 which has a foreign key that
> > referenced to the IDENTITY column in TABLE1. When the IDENTITY column in
> > TABLE1 is adjusted after import, the import of TABLE2 will fail since the
> > References is messed up already. Am I doing it right or is there other ways
> > to do import and exports of Tables with references.
> >
> > Thank you!
>
> Since you specified BY DEFAULT that means you can import TABLE1 using
> the existing id values so that when you import TABLE2 you shouldn't have
> any referential integrity issues. But, of course, you'll want to export
> the id values from TABLE1.
>
> I was able to do this using Derby's SYSCS_UTIL.SYSCS_IMPORT_DATA
> procedure.
>
Let me further point out that you should read the fine print for
generated-column-spec in the Derby SQL manual under Create Table /
column-definition / generated-column-spec, especially the part about
using a START WITH clause.
Re: Importing tables which have references to other tables
Posted by Tim Watts <ti...@cliftonfarm.org>.
On Thu, 2012-06-14 at 23:26 -0700, jaz1988 wrote:
> Hi,
>
> I have a table, TABLE1 which has a column which is GENERATED BY DEFAULT AS
> IDENTITY. I managed to export this table and import into an existing DB with
> the same table. The import is able to adjust the IDENTITY column accordingly
> with regards to the existing data in the table.
>
> The problem is, I have another table, TABLE2 which has a foreign key that
> referenced to the IDENTITY column in TABLE1. When the IDENTITY column in
> TABLE1 is adjusted after import, the import of TABLE2 will fail since the
> References is messed up already. Am I doing it right or is there other ways
> to do import and exports of Tables with references.
>
> Thank you!
Since you specified BY DEFAULT that means you can import TABLE1 using
the existing id values so that when you import TABLE2 you shouldn't have
any referential integrity issues. But, of course, you'll want to export
the id values from TABLE1.
I was able to do this using Derby's SYSCS_UTIL.SYSCS_IMPORT_DATA
procedure.