Re: Transfering tables
Michel,
Try writing a stored procedure using the following logic:
1) Create table tableorder (tabseq serial, tabid integer, tabname
varchar(18));
Create table tableorder_new (tabseq integer, tabid integer, tabname
varchar(18));
2) Get all tables with no constraints.
insert into tableorder
select 0, t.tabid, t.tabname
from systables t
where t.tabid not in
(select tabid
from sysconstraints
where constrtype = 'R')
and t.tabid not in
(select ptabid from sysreferences)
and t.tabid > 99;
3) Get all tables at the top of the constraint hierarchy tree.
insert into tableorder
select distinct 0, t.tabid, t.tabname
from systables t,
sysreferences r
where t.tabid = r.ptabid
and t.tabid not in
(select tabid
from sysconstraints
where constrtype = 'R')
and t.tabid > 99;
4) Loop through the rest of the hierarchy (level by level) to get the
rest of the constraints.
Loop until no rows inserted
# You cannot insert into a table used in a subquery. So insert into
# another table then insert the new rows into tableorder.
insert into tableorder_new
select distinct 0, t1.tabid, t1.tabname
from systables t1,
sysconstraints c1,
sysreferences r1
where t1.tabid = c1.tabid
and c1.constrid = r1.constrid
# make sure you already have this parent
and r1.ptabid in
(select o2.tabid from tableorder o2)
# Make sure that this table is not referenced by another table
# currently not loaded into tableorder. If it is this table will be
# picked up later.
and t1.tabid not in
(select c3.tabid
from sysconstraints c3,
sysreferences r3
where c3.constrid = r3.constrid
and r3.ptabid not in
(select o4.tabid from tableorder o4))
and t.tabid > 99;
insert into tableorder
select * from tableorder_new;
delete from tableorder_new where 1 = 1;
5) You may have missed some tables in the previous loop. If they were
a leaf table constrained by multiple parents.
insert into tableorder_new
select 0, tabname
from systables t,
sysconstraints c
where t.tabid = c.tabid
and t.tabid not in
(select o.tabid from tableorder o)
and t.tabid > 99;
insert into tableorder
select * from tableorder_new;
delete from tableorder_new where 1 = 1;
6) Create your unload script.
select 'unload to '||trim(tabname)||'.unl',
' select * from '||trim(tabname)||';',
tabseq
from tableorder
order by tabseq;
7) Delete the tabseq line from the previous result.
I have tested each of the sql's individually and they worked. I don't
have a complex constraint hierarchy like you so I couldn't test the
whole logic. I hope this works for you.
Regards
David Ashby
______________________________ Reply Separator _________________________________
Subject: Transfering tables
Author: <Michel.Auger@cgi.ca (M Auger)> at WCA-INET
Date: 17/12/97 10:02 PM
We need to transfer some tables between 2 databases on a regular basis. Most
tables have referential constraints, there are hundred of tables we have to
look for. Is there an easy way to list the tables in order they should be
inserted first?
Thanks
Michel