How to insert data from one table to another?

How to insert data into one table from multiple table using oracle procedure

• I have 6 different tables which has dependency with each other. I am trying to insert selected columns from all these 6 tables into a single table. I have to pass a parameter to select the data from one table based on the output of the table reaming tables data's are fetched. If no records match then null should be passed. I am trying to write a procedure using 6 cursor and pass the values of one cursor to others. Somebody Pls help me achieve this. Here is my simplifed verison of my table Table Name table type ========== ========== CRM_CLAIM_INT_DETAILS_VIEW Linked_Table INTRFC_MOTOR_NOTIFICATION_VIEW Linked_Table Intrfc_Motor_Office_Master Linked_Table INTRFC_MOTOR_RISK_VIEW Linked_Table Intrfc_Policy_view Linked_Table MO_CLAIM_MASTER Table Selected Fields =============== CRM_ACC_DATE_TIME,CRM_GEN_REP_NAME,CRM_SYSDATE policy_number Parent_office_name CHASSIS_NO,ENGINE_NO,MAKE,MODEL,REGISTRATION_NO AGENT_NAME,Branch_Office Description,Insured_Address,Insured_Name,PRODUCT_CODE,RISK_START_DATE,RISK_START_TIME claim_no,DATE_OF_NOTIFICATION,notified_by,VEHICLE_TYPE Condition ========= mo_claim_master.crm_sl_no = CRM_CLAIM_INT_DETAILS_VIEW.crm_sl_no INTRFC_MOTOR_NOTIFICATION_VIEW.claim_no = mo_claim_master.claim_no intrfc_policy_view.branch_office_code = Intrfc_Motor_Office_Master.office_code INTRFC_MOTOR_RISK_VIEW.refernce_num = intrfc_policy_view.num_reference_num intrf_notification_view.refernce_num = intrfc_policy_view.num_reference_num claim_ref_no=:claim_ref_no INSERT INTO CFT_Table i have created a procedure for the same but still i am not able to get the null values. Is this a right way to get this output pl. suggest me. CREATE OR REPLACE PROCEDURE TEST( para_claim_ref_no IN VARCHAR2 ) AS CURSOR c1 IS SELECT claim_no, DATE_OF_NOTIFICATION, VEHICLE_TYPE, notified_by, notif_crm_sl_no FROM mo_claim_master WHERE claim_ref_no=para_claim_ref_no; t1 c1%rowtype; claim_num_var VARCHAR2(50); notif_crm_sl_no_var NUMBER; CURSOR c2 IS SELECT policy_num, reference_num FROM INTRFC_MOTOR_NOTIFICATION_VIEW WHERE claim_no=claim_num_var; t2 c2%rowtype; num_reference_num_var NUMBER; CURSOR c3 IS SELECT BRANCH_OFFICE_CODE, Branch_Office_Desc, Insured_Name, Insured_Address, RISK_START_DATE, RISK_START_TIME, PRODUCT_CODE, AGENT_NAME FROM intrfc_policy_view WHERE num_reference_number=num_reference_num_var; t3 c3%rowtype; branch_office_code_var VARCHAR2(30); CURSOR c4 IS SELECT OFFICE_CODE FROM INTRFC_MOTOR_OFFICE_MASTER WHERE office_code = branch_office_code_var; t4 c4%rowtype; CURSOR c5 IS SELECT MAKE, MODEL, ENGINE_NO, CHASSIS_NO, REGISTRATION_NO FROM INTRFC_MOTOR_RISK_VIEW WHERE reference_num=num_reference_num_var; t5 c5%rowtype; CURSOR c6 IS SELECT CRM_ACC_DATE_TIME, CRM_GEN_REP_NAME FROM CRM_CLAIM_INT_DETAILS_VIEW WHERE crm_slno=notif_crm_sl_no_var; t6 c6%rowtype; BEGIN OPEN c1; FETCH c1 INTO t1; CLOSE c1; claim_num_var := t1.claim_no; notif_crm_sl_no_var := t1.notif_crm_sl_no; OPEN c2; FETCH c2 INTO t2; CLOSE c2; num_reference_num_var := t2.reference_num; OPEN c3; FETCH c3 INTO t3; CLOSE c3; branch_office_code_var := t3.BRANCH_OFFICE_CODE; OPEN c4; FETCH c4 INTO t4; CLOSE c4; OPEN c5; FETCH c5 INTO t5; CLOSE c5; OPEN c6; FETCH c6 INTO t6; CLOSE c6; dbms_output.put_line(t1.claim_no); dbms_output.put_line(t1.DATE_OF_NOTIFICATION); dbms_output.put_line(t1.VEHICLE_TYPE); dbms_output.put_line(t1.notified_by); dbms_output.put_line(t2.policy_num); dbms_output.put_line(t3.Branch_Office_Desc); dbms_output.put_line(t3.Insured_Name); dbms_output.put_line(t3.Insured_Address); dbms_output.put_line(t3.RISK_START_DATE); dbms_output.put_line(t3.RISK_START_TIME); dbms_output.put_line(t3.PRODUCT_CODE); dbms_output.put_line(t3.AGENT_NAME); dbms_output.put_line(t4.office_code); dbms_output.put_line(t5.MAKE); dbms_output.put_line(t5.MODEL); dbms_output.put_line(t5.ENGINE_NO); dbms_output.put_line(t5.CHASSIS_NO); dbms_output.put_line(t5.REGISTRATION_NO); dbms_output.put_line(t6.CRM_ACC_DATE_TIME); dbms_output.put_line(t6.CRM_GEN_REP_NAME); END TEST;

• Answer:

  No one posted an answer, so I'll add one here. When you're talking about multiple cursors, it sounds like you want to loop over each row in each cursor, inserting and updating as you go. That's about the worst possible solution. You want an INSERT ... SELECT. Tom Kyte explains this here: http://tkyte.blogspot.com/2006/10/slow-by-slow.html I'm not going to write the entire insert statement here, because you have a ton of table and columns, but basically you create an INSERT statement based on a SELECT: INSERT INTO CFT_Table ( ... column names ... ) SELECT ( ... column names ... ) FROM MO_CLAIM_MASTER LEFT JOIN Intrfc_Policy_view ON ( ... join condition ... ) LEFT JOIN INTRFC_MOTOR_RISK_VIEW ON (... join condition ... ) I used LEFT JOINs because you said you wanted NULL values. I'm assuming MO_CLAIM_MASTER has one row for each row you want inserted.