Insert Data Into 2 tables at 1 shot

Hi All,

Have you ever wondered how to insert data into 2 tables from one table at one shot?

oracle provides a feature called INSERT ALL.....

I have used the above concept like this.

I insert good data into the fact table along with their rowid's into rowid_tab table at one shot.

Then i insert the data into reject table whose rowid's are not present in the rowid_tab.

ex: To Insert data into fact table and rowid table....
INSERT /*+ APPEND */ ALL
INTO TABLE_FACT(col1,
col2,
col3)
VALUES( col1.val,
col2.val,
col3.val )
INTO ROWID_TAB(ROWID_COL) VALUES(ROWID)
SELECT col1,
col2,
col3,
rowid
FROM TABLE_STG
WHERE Cond1 AND Cond2...;

To insert data into Reject table

INSERT /*+ APPEND */ ALL
INTO TABLE_FACT_REJ(col1,
col2,
col3)
VALUES( col1.val,
col2.val,
col3.val )
SELECT col1,
col2,
col3,
rowid
FROM TABLE_STG
WHERE rowid NOT IN (SELECT ROWID_COL
FROM ROWID_TAB);

Another improvisation can be to make ROWID_TAB as a Global Temporary table.

For more imformation on this,please refer the links below
1.http://certcities.com/editorial/columns/story.asp?EditorialsID=51
2.http://www.dba-oracle.com/t_global_temporary_tables.htm

Hope this helps....