Inserting one record at a time to multiple tables from a table

Question

Hi,

I have one flat MAIN_TABLE. I need to insert the records from this table to multiple TABLES.

eg.

MAIN_TABLE
col1, col2, col3, col4, col5

PARENT1_TABLE
PT1_ID(PK), col1,col2

PARENT2_TABLE
PT2_ID(PK), col3,col4

PARENT2_CHILD_TABLE
P2C_ID(PK), PT2_ID(FK), col5, col6

so on.

The goal is, I have to move the record from that flat MAIN_TABLE to the relational structured, I defined above.

Any help would be highly appreciated?

Thank you

Answer 1

If this was in SQL Server you could create a view of your MAIN_TABLE, then write an INSTEAD OF INSERT trigger on the view. That way, as you INSERT a record into your MAIN_TABLE view, your trigger can parse it into your child tables. Link goodness.

Answer 2

In Oracle you can do Multi-Table inserts. Create a dummy error logging table

create global temporary table err_dump
  (ORA_ERR_NUMBER$   NUMBER,
  ORA_ERR_MESG$     VARCHAR2(2000),
  ORA_ERR_ROWID$    UROWID(4000),
  ORA_ERR_OPTYP$    VARCHAR2(2),
  ORA_ERR_TAG$      VARCHAR2(2000));

Then add a unique key on (col1,col2) for parent1 and (col3,col4) for parent2. Use the multi-table insert to load into the two parent tables. The LOG ERRORS INTO clause will mean that any duplicates going into parent tables will be kicked out.

INSERT ALL
      INTO parent1_table (pt1_id, col1,col2)
      VALUES (rn, col1,col2)
      LOG ERRORS INTO err_dump REJECT LIMIT 99999999
      INTO parent2_table (pt2_id, col3, col4)
      VALUES (rn, col3, col4)
      LOG ERRORS INTO err_dump REJECT LIMIT 99999999
   SELECT rownum rn, col1, col2, col3, col4
   FROM MAIN_TABLE;

Finally, do a select from MAIN, joined to parent1_table and parent2_table to get the new PKs, and that is a simple insert into PARENT2_CHILD_TABLE