Hi JK, you can use a single query: Insert Into table3(patient_id, code) (Select patient_id, code1 From tmp_table1 natural join tmp_table2 Union All Select patient_id, code2 From tmp_table1 natural join tmp_table2 Union All Select patient_id, code3 From tmp_table1 natural join tmp_table2 );
or using a cursor in plsql as well -- VinhBuu To On Tue, Aug 2, 2011 at 2:46 AM, JK <[email protected]> wrote: > I have 3 tables as follows. I have to load the third table in a > specific way. > > > CREATE TABLE tmp_TABLE1 ( > patient_id NUMBER(1), > agency_number VARCHAR2(5), > case_number VARCHAR2(5) > ); > INSERT INTO table1 VALUES (1, '1111', '1212'); > INSERT INTO table1 VALUES (2, '2222', '2323'); > INSERT INTO table1 VALUES (3, '3333', '2424'); > INSERT INTO table1 VALUES (4, '4444', '3434'); > INSERT INTO table1 VALUES (5, '5555', '4545'); > INSERT INTO table1 VALUES (6, '6666', '5656'); > Create table tmp_table2( > agency_number VARCHAR2(5), > case_number VARCHAR2(5), > code1 number(2), > code2 number(2), > code3 number(2) > ); > INSERT INTO table2 VALUES ('1111', '1212',01,02,03); > Insert into table2 values ('2222', '2323',02,04,05); > Insert into table2 values ('3333', '2424',02,05,06); > > Create table table3( > patient_id NUMBER(1), > code number(2) > ); > > I have to load the table3 from tmp_table1 and tmp_table2: On querying > table3 it should look like the following. Is there as efficient way of > doing this in PL SQL, whether using a cursor or not. > select * from table3 > PATIENT_ID.................................. CODE > 1.....................................................1 > 1.....................................................2 > 1.....................................................3 > 2.....................................................2 > 2.....................................................4 > 2.....................................................5 > 3.....................................................2 > 3.....................................................5 > 3.....................................................6 > > I am using Oracle 10g. > > Your help will be appreciated. > > > -- > You received this message because you are subscribed to the Google > Groups "Oracle PL/SQL" group. > To post to this group, send email to [email protected] > To unsubscribe from this group, send email to > [email protected] > For more options, visit this group at > http://groups.google.com/group/Oracle-PLSQL?hl=en > -- You received this message because you are subscribed to the Google Groups "Oracle PL/SQL" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [email protected] For more options, visit this group at http://groups.google.com/group/Oracle-PLSQL?hl=en
