Home » SQL & PL/SQL » SQL & PL/SQL » I want to load data from 3 tables in schema1 into a table in schema2 using a CURSOR..
I want to load data from 3 tables in schema1 into a table in schema2 using a CURSOR.. [message #18611] Thu, 07 February 2002 06:32 Go to next message
Sabrina
Messages: 76
Registered: February 2002
Member
I want to load data from 3 tables in schema1 into a table in schema2.

SCHEMA1:
TABLE1
PK1,C2,C3,C4 etc..
TABLE2
PK1/FK1,C5,C6 etc..
TABLE3
PK1/FK1,C7,C8 etc..

TABLE1 is the parent table with PrimaryKey PK1
TABLE2 is a child table to TABLE1 with PK1 migrated as a foreignkey which is a PK too.
TABLE3 is a child table to TABLE1 with PK1 migrated as a foreignkey which is a PK too.

SCHEMA2
TABLE1
PK1,C2,C3,C4,C5,C6,C7,C8 etc..

How do i load data into SCHEMA2.TABLE1..
I know, i will need a cursor.. but i have never worked with cursors before..
so please give me an example.
should i select the values from SCHEMA1.TABLE1 first and then
based on the PK select the remaining values from SCHEMA2.TABLE2 and SCHEMA3.TABLE3..
and then insert into SCHEMA2.TABLE1?
PLEASE HELP ME!!
Thanks in advance.
Re: I want to load data from 3 tables in schema1 into a table in schema2 using a CURSOR.. [message #18612 is a reply to message #18611] Thu, 07 February 2002 06:55 Go to previous messageGo to next message
pratap kumar tripathy
Messages: 660
Registered: January 2002
Senior Member
Hi,

you can avoid using cursor .see the following statement, it will do the job.

insert into schema2.table1
(PK1,C2,C3,C4,C5,C6,C7,C8)
as
select a.pk,a.c2,a.c3,a.c4,
b.c5,b.c6,
c.c7,c.c8
from schema1.table1 a,
schema1.table2 b,
schema1.table3 c
where a.pk=b.pk
and a.pk=c.pk


hope it helps

cheers
pratap
What do you mean MIKE? [message #18613 is a reply to message #18611] Thu, 07 February 2002 07:15 Go to previous messageGo to next message
Sabrina
Messages: 76
Registered: February 2002
Member
I have about half a million rows to load and then i have to do this every night..
Can you give me an example?
I actually will have to do a 3 way join [message #18615 is a reply to message #18611] Thu, 07 February 2002 07:18 Go to previous message
Sabrina
Messages: 76
Registered: February 2002
Member
I actually will have to do a 3 way join and then do a row by row insert
because if there is bad data, i want it to go to a different table..
Previous Topic: Primary key & Column name
Next Topic: cursors and procedure problem,urgent!!
Goto Forum:
  


Current Time: Tue Sep 28 11:35:20 CDT 2021