On Apr 2, 1:25 pm, Sankar <pr.san...@[EMAIL PROTECTED]
> wrote:
> Hi,
> I am new to Database..I donot want insertion order of rows or
> records.. I need insertion order of tables..
>
> Consider if i am migrating from sourceDB to targetDB..through
> programatically
>
> First i will create DB and all tables in the targetDB..
>
> Then I have to move all the datas from sourceDB to targetDB... Here
> for moving i need insertion order of tables(to take care of forign key
> violations .. parent tables first thn child tables)
>
I was doing that a couple of times, it required a little bit of
programming.
I used to create additional table called dictionary. It contained
following columns:
create table dictionary (
source_id bigint,
target_id bigint,
data_class varchar(16)
);
I "walked" through the source tables (the order in which tables are
browsed is im****tant because of
foreign keys). For each record I put ID into source_id, and add
data_class which is the identifier of the data type,
it could be the name of the source table. 'target_id' contained an ID
of the row in the target table.
When I encountered a column that had a foreign key constrain, I looked
at the dictionary to find the target
ID that corresponds to the ID found in source table.
For example there were students which were organized in groups. So I
processed 'group' table first, and student data
after it. The dictionary looked like this:
SOURCE_ID TARGET_ID DATA_CLASS
1 25 'group'
2 99 'group'
5 78 'student'
11 44 'student'
So, if student ID=5 belonged to the group ID=2 in old database, I
inserted the same student, with group_ID=99, which is
the corresponding ID in the new database. The student was assigned the
ID=78 by the database engine, which is recorded in the dictionary,
too.
I used to do it in Groovy, now I would do that in Beanshell.
It pays off to have a im****t script if you are doing the transfer from
the old application.
It is often the case that client does not have data in 100% perfect
shape, which becomes obvious during transition,
so you might be asked to convert data more than once.
B
|
