How to insert into multiple tables from one table through procedures?

Question

There are 4 tables

Table1(Column11, Column12, Column13)
Table2(Column21, Column22)
Table3(Column31, Column32,column33)
Table4(Column21, Column22)

And the following Mapping Table:

Table5(Sourcetable,Source column ,Destination table ,Destination column)

How to insert data from Table1 as source table and destination table as Table2,table3,table,4, Through Procedures?

I am using oracle 11g. Please help to achieve this!

What have you tried so far? What exactly do you want? A procedure that reads out the source table/column and destination table/column from table5 and then executes the copy? — Armunin, Dec 02 '13 at 12:54

Armunin · Answer 1 · 2013-12-02T13:20:45.793

0

If I understand your question, this is what you are looking for:

CREATE OR REPLACE PROCEDURE cpy_table
AS
CURSOR targ_dest_relation IS
    SELECT sourcetable, sourcecolumn, destinationtable, destinationcolumn FROM table5;

BEGIN
    FOR rec IN targ_dest_relation loop
        execute immediate 'insert into ' || rec.destinationtable || '(' || rec.destinationcolumn || ') select ' || rec.sourcecolumn || ' from ' || rec.sourcetable;
    END loop;
END;
/

SQL Fiddle

edited Dec 02 '13 at 13:20

answered Dec 02 '13 at 13:06

Armunin

986
7
18

this cursor is helpful but : – user3057455 Dec 03 '13 at 05:50
I think you are missing some text. But what? – Armunin Dec 03 '13 at 07:39

How to insert into multiple tables from one table through procedures?

1 Answers1