Re: Problem with Exporting/Importing data from multiple tables?

On 30 Jun 2004 04:44:24 -0700, karthikd22_at_hotmail.com (Karthik D) wrote:

>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:
>
>1. Does plain-text csv format would be easy for this?
>

Not necessary, plain sql would do. If the destination tables are in a different schema, prefix the table name with the username. If the destination tables are in a different database set up a database link.

>2. If using csv, how would I be able to differentiate the columns
>accordingly?
>
>3. What would be the good mechanism to import these data back into
>destination
> tables?
>
>4. Tools/Scripts/Packages which would help me in doing this migration?
>

plain sql
>there are around 70k records in the source tables!

peanuts.
>
>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.

Beg to differ. This definitely IS a homework problem.

>
>Thanks & Regards,
>Karthik

--
Sybrand Bakker, Senior Oracle DBA