SEO

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?

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?

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

On 30 Jun 2004 04:44:24 -0700, karthikd22@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