Re: Data load question

From: Mike Spalinger <mike.spalinger_at_gmail.com>
Date: Fri, 7 Apr 2006 14:26:55 -0600
Message-ID: <98befd000604071326u470b5532k4d466be6a9e0b4d8@mail.gmail.com>

Hi Sandeep,

Here's an example of an "insert all":

create table a (col1 varchar2(10), col2 varchar2(10));

insert into a values ('A1','B1');
insert into a values ('A1','B2');
insert into a values ('A2','B3');
insert into a values ('A2','B4');
insert into a values ('A3','B5');

create table b (id number, col1 varchar2(10)); create table c (id number, col2 varchar2(10), b_id number);

insert all
when table_num = 1 then

   into b (id, col1)
   values (a_seq, col1)
when table_num >= 1 then

   into c (id, col2, b_id)
   values (b_seq, col2, a_seq)
select col1,

       col2,
       row_number() over (partition by col1 order by col1) as table_num,
       dense_rank() over (order by col1) as a_seq,
       row_number() over (order by col1, col2) as b_seq

  from a;

SQL> select * from a;

COL1 COL2
---------- ----------

A1         B1
A1         B2
A2         B3
A2         B4
A3         B5

SQL> select * from b;

        ID COL1
---------- ----------

         1 A1
         2 A2
         3 A3

SQL> select * from c;

        ID COL2 B_ID
---------- ---------- ----------

         1 B1                  1
         2 B2                  1
         3 B3                  2
         4 B4                  2
         5 B5                  3

Mike

On 4/7/06, Sandeep Dubey <dubey.sandeep_at_gmail.com> wrote:
>
> I need to load data from one table to two tables. It is kind of
> noramlizing the data.
>
> As an example:
> Source table:
> Table A(col1, col2)
> A1,B1
> A1,B2
> A2,B3
> A2,B4
> A3,B5
>
> From this table I need to insert into Table B and C
>
> I wan to insert data into B and C as:
>
> Table B(id, col1) id is synthetic pk from sequence
> 1,A1
> 2,A2
> 3,A3
>
> Table C(id, col2,B_id) id is pk, b_id is fk to B.id
> 1,B1,1
> 2,B2,1
> 3,B3,2
> 4,B4,2
> 5,B5,2
>
> Source table A has 2 million rows. I am trying to do through a single
> select on table A and using insert into .. select. Or using FORALL.
>
> Any good ideas on how to do this?
>
> Regards
>
> Sandeep
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Apr 07 2006 - 15:26:55 CDT