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

  ¶ 5/21/2008 05:17:00 PM