Problem with Exporting/Importing data from multiple tables?

From: Karthik D <karthikd22_at_hotmail.com>
Date: 30 Jun 2004 04:44:24 -0700
Message-ID: <e023a2c9.0406300344.480a8e57@posting.google.com>

Hello All,
I apologize if this question would have been asked by people many times already in this group.I tried to search for some information on this, but sometimes you get lost with lot of information infront of you.

My knowledge of Oracle is limited to basic SQL queries, bit of PL/SQL and some basic administration.

The problem I have is I need to migrate some information from some set of tables to a different set of tables.

The data which I need to migrate is distributed in different tables.

For example:

   STable1: usrid,usrname
   STable2: grpid,grp_name
   STable3: uid,gid,memb_name

Here gid is something like foreign key and has the value has grpid.Same applies to uid(f.k)

So each table has some values like this:

STable1

usrid usrname
------ --------

101      jack
102      rose
103      peter
104      mary

STable2

grpid grp_name
------ --------

10      hr
11      mktg
12      finance

STable3

uid gid memb_name
--- --- ----------
101 10 rose
101 10 peter
101 11 mary

102 12 jack
102 10 peter

Columns need to be fetched from each table is:

STable 1 : usrid
STable 2 : grpid,grp_name
STable 3 : memb_name

The file which contains the data should be:

101,10,hr,rose,peter =>record 1
101,11,mktg,mary
102,12,finace,jack
102,10,hr,peter

The meaning is user_id:101 has two groups namely hr and mktg and under "hr" he has two members namely "rose" and "peter" and under "mktg" he has only one member "mary"

These data needs to be migrated to the destination tables as described below:

DTable1: group_id,group_name
DTable2: grp_id, usr_id, mem_name

DTable 1 (The group_id is incremented according to no.of members in each group)

group_id group_name
========= ==========

10          hr
11          hr
12          mktg
13          finance
14          hr

DTable 2

grp_id usr_id mem_name
------- ------ ---------

10       101     rose
11       101     peter
12       101     mary
13       102     jack
14       102     peter

My questions are:

Does plain-text csv format would be easy for this?
If using csv, how would I be able to differentiate the columns accordingly?
What would be the good mechanism to import these data back into destination tables?
Tools/Scripts/Packages which would help me in doing this migration?

there are around 70k records in the source tables!

I hope I could able to articulate my problem clearly.Hope somebody could give me suggestions/ideas.Thanks for your time.

P.S: This is not a homework problem.

Thanks & Regards,
Karthik Received on Wed Jun 30 2004 - 06:44:24 CDT