Multi Table INSERTs
I learned something new today! Multi Table INSERTs.
I was trying to capture the new records into a staging table using RETURNING BULK COLLECT INTO, but that doesn't work (yet).
I remembered reading about Multi Table INSERTs, but never had the need for using them, so never bothered to learn it.
CREATE TABLE t
(
x NUMBER PRIMARY KEY,
y NUMBER,
z NUMBER
);
CREATE TABLE s
(
x NUMBER PRIMARY KEY
);
INSERT ALL
INTO t ( x, y, z )
VALUES ( myrownum, ran1, ran2 )
INTO s ( x )
VALUES ( myrownum )
SELECT
rownum myrownum,
dbms_random.value ran1,
dbms_random.value ran2
FROM dual
CONNECT BY LEVEL < 101;
CJUSTICE@ORA10GR2>SELECT COUNT(*) FROM t;
COUNT(*)
----------
100
1 row selected.
Elapsed: 00:00:00.01
CJUSTICE@ORA10GR2>SELECT COUNT(*) FROM s;
COUNT(*)
----------
100
1 row selected.
Elapsed: 00:00:00.02
Awesome!
Labels: database, datawarehouse, sql
