A friend who does not blog wrote this up for his co-workers, it is straight forward but useful. Enjoy.
A few days ago someone asked if it was possible in an oracle DB to insert into multiple different tables from a single query. I said "Yes of course", they asked "So, How?", I of course said "RTFM". Well, here it is a little easier to read than in "The Fine Manual"
This works all the way back to Oracle 8 so feel free to test it out. But, since it does drop objects, please do it in a test location. Personally I recommend everybody download and install oracle XE ( http://www.oracle.com/technology/software/products/database/xe/htdocs/102xewinsoft.html ) on your PC, gives you a nice safe place to work, play and learn, and as an added bonus Oracle XE comes with Application Express (APEX) already installed and ready to go. Now that I said that, I don't support PC's so who knows what it will change on the configuration for on your PC. Do so at your own risk.
Simply creating some test tables and a sequence for later use in this example.
SQL> CREATE TABLE BASETABLE (BASEID NUMBER PRIMARY KEY,BASEDATA VARCHAR2(30));
Table created.
Elapsed: 00:00:00.03
SQL> CREATE TABLE DEST1 (DESTID NUMBER PRIMARY KEY,BASEID NUMBER UNIQUE,BASEDATA VARCHAR2(30));
Table created.
Elapsed: 00:00:00.01
SQL> CREATE TABLE DEST2 (DESTID NUMBER PRIMARY KEY,BASEID NUMBER UNIQUE,BASEDATA VARCHAR2(30));
Table created.
Elapsed: 00:00:00.03
SQL> CREATE TABLE DEST3 (DESTID NUMBER PRIMARY KEY,BASEID NUMBER UNIQUE,BASEDATA VARCHAR2(30));
Table created.
Elapsed: 00:00:00.06
SQL> CREATE TABLE DEST4 (DESTID NUMBER PRIMARY KEY,BASEID NUMBER UNIQUE,BASEDATA VARCHAR2(30));
Table created.
Elapsed: 00:00:00.03
SQL>
SQL>
SQL> CREATE SEQUENCE DESTID_SEQ;
Sequence created.
Elapsed: 00:00:00.00
SQL>
Insert some data into the base table for use later
SQL> INSERT INTO BASETABLE SELECT ROWNUM*-1,DBMS_RANDOM.STRING('A',30) FROM DUAL CONNECT BY LEVEL <=500;
500 rows created.
Elapsed: 00:00:00.09
SQL> COMMIT;
Commit complete.
Elapsed: 00:00:00.00
Now the actual insert, you can see the WHEN and ELSE clause of the INSERT statement. You can have as many of those as you want, each inserting different combination of columns for the VALUES section. In this case, I am using a sequence to satisfy the primary key of the DESTx table and then the two column names from the select clause at the end.
SQL>
SQL> INSERT ALL
2 WHEN BASEID=-1 THEN INTO DEST1 VALUES (DESTID_SEQ.NEXTVAL, BASEID,BASEDATA)
3 WHEN BASEID=-10 THEN INTO DEST2 VALUES (DESTID_SEQ.NEXTVAL, BASEID,BASEDATA)
4 WHEN BASEID IN (-100,-200,-300,-400) THEN INTO DEST3 VALUES (DESTID_SEQ.NEXTVAL, BASEID,BASEDATA)
5 ELSE INTO DEST4 VALUES (DESTID_SEQ.NEXTVAL, BASEID,BASEDATA)
6 SELECT BASEID,BASEDATA FROM BASETABLE ORDER BY BASEID DESC;
500 rows created.
Elapsed: 00:00:00.01
SQL> COMMIT;
Commit complete.
Elapsed: 00:00:00.00
Now to show what happened. From the following query you can see that the BASEID of -1 was inserted and the DESTID was the very first record in the insert as shown by the sequence value of 1.
This following data was inserted based on the
WHEN BASEID=-1 THEN INTO DEST1 VALUES (DESTID_SEQ.NEXTVAL, BASEID,BASEDATA)line in the insert statement.
SQL> SELECT * FROM DEST1;
DESTID BASEID BASEDATA
---------- ---------- ------------------------------
1 -1 uzvIPoJevGslWNzcsEULVsOIHrWtkA
Elapsed: 00:00:00.00
From the following query you can see that the BASEID of -10 was inserted, and was the 10th line in the select query return result. This was inserted based on the line
WHEN BASEID=-10 THEN INTO DEST2 VALUES (DESTID_SEQ.NEXTVAL, BASEID,BASEDATA)in the insert statement.
SQL> SELECT * FROM DEST2;
DESTID BASEID BASEDATA
---------- ---------- ------------------------------
10 -10 AzRwrjLpzvxtacxBOitYhGDGDuKmaU
Elapsed: 00:00:00.01
From the following query you can see that the BASEIDs of -100,-200,-300 and -400 were inserted.This was inserted based on the
line WHEN BASEID IN (-100,-200,-300,-400) THEN INTO DEST3 VALUES (DESTID_SEQ.NEXTVAL, BASEID,BASEDATA)in the insert statement.
SQL> SELECT * FROM DEST3;
DESTID BASEID BASEDATA
---------- ---------- ------------------------------
100 -100 uJixIEqFTeZEBDOCPYkJgyipInuTdt
200 -200 ikmTNgdjGTjkINEGbxEFifWAetPBMt
300 -300 gKcFyianMOtGzdJzVlkjqaLPiwBkic
400 -400 prucyUxTqhPhUTzarsJRyFQYlOUlWz
Elapsed: 00:00:00.01
From the following query you can see the remainder of the records in the BASETABLE were inserted into the DEST4 table. If you look you can see that BASEID of -1,-10,-100 and -200 are missing. You will have to trust me that -300 and -400 are missing in the result set as well, but I didn't want this running too long.
SQL> SELECT * FROM DEST4 ORDER BY DESTID;
DESTID BASEID BASEDATA
---------- ---------- ------------------------------
2 -2 fPNMkRbJAEoeaWejzrAigZjKqZVzUl
3 -3 NDmRQNKmPhAnzfuWhLQDnWIcRVpjLF
4 -4 DoNnVEskItQAfANavQVHdJWdOeZbAc
5 -5 SNacUWsrPCPyLwDBxEtndSsiiSTmPW
6 -6 gLxiVlWXsdcLPhDgLThISCutKBfuOj
7 -7 sZCNlljiTveZPIUgyEBPalpJPrMdck
8 -8 UOwvqNxyPXcpsxRmjsxLQGfEsHQOqO
9 -9 WDwQqUnMHjDautMrYYBMCcjIoNWMKg
11 -11 BOfKwqtFZWQuLVEHFhMRHrfBGyeTfQ
<SNIP>
99 -99 VjmavGgzdQroTHutlhcOQjiqlTiLHW
101 -101 cjuHxrklWRaQmRJZyVShliswLRCgBm
<SNIP>
199 -199 xvaXYHPkexmFOkXCDBOODqjEatyMwY
201 -201 fXwQaaSTWAEDrYDqnRHVxLqcQEkbCZ
<SNIP>
500 -500 eLqsjEKEzWTmQUTsEtHFcRVEkEiQZz
494 rows selected.
Elapsed: 00:00:01.06
Now simply the cleanup.
SQL> DROP SEQUENCE DESTID_SEQ;
Sequence dropped.
Elapsed: 00:00:00.03
SQL> DROP TABLE BASETABLE;
Table dropped.
Elapsed: 00:00:00.03
SQL> DROP TABLE DEST1;
Table dropped.
Elapsed: 00:00:00.03
SQL> DROP TABLE DEST2;
Table dropped.
Elapsed: 00:00:00.04
SQL> DROP TABLE DEST3;
Table dropped.
Elapsed: 00:00:00.03
SQL> DROP TABLE DEST4;
Table dropped.
Elapsed: 00:00:00.01
SQL>
SQL> SPOOL OFF
