Wednesday, March 07, 2007

Multi table INSERT in Oracle

I have given an example which demonstrates the Oracle’s strength to insert data into multiple tables using single SQL statement

SQL> create table temp1(a number(2),b varchar2(5));

Table created.

SQL> create table temp2(a number(2),b varchar2(5));

Table created.

SQL> insert all
when rownumt <10>=10 then into temp2 values(rownumt, String)
SELECT ROWNUM rownumt,DBMS_RANDOM.STRING('A',5) String FROM DUAL CONNECT BY LEVEL <=20;

20 rows created.

SQL> select * from temp1;

A B
---------- -----
1 yKXDU
2 iQPtH
3 kbBcy
4 lmUoZ
5 JiLBi
6 udQEl
7 HVDzE
8 ULixZ
9 gNUSO

9 rows selected.

SQL> select * from temp2;

A B
---------- -----
10 RYyLv
11 LnkFo
12 gEBUZ
13 TYlxG
14 pvMKv
15 KSiIr
16 Xzkzt
17 ppEpq
18 wCIAY
19 tfGiX
20 nCPeZ

11 rows selected.

SQL>

No comments: