This has much information. consider using Bulk inserts. http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1583402705463
On Mon, Aug 1, 2011 at 6:28 PM, Jyothi Kavasseri <[email protected]> wrote: > Thank you for your response. > > If the count of the patient is limited to a small number, single query is > an option. But the count can go beyond some millions. Even using a cursor > might cause performance issues. I wonder if there are more efficient > functions in Oracle to resolve this issue. If so, I would appreciate if you > could provide some examples of using those functions. > > > On Mon, Aug 1, 2011 at 9:05 PM, Vinh Buu To <[email protected]> wrote: > >> 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 >> > > -- > 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
