ORACLENERD
 
Wednesday, May 21, 2008
  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: , ,

 
Comments:
I like it!
 
Post a Comment



Links to this post:

Create a Link



<< Home
Google



How To
Parallel Processing: DBMS_JOB
SAS: Create Dataset From Oracle Table
Instrumentation: DBMS_APPLICATION_INFO
DBMS_CRYPTO

Popular
AppDev vs DataDev
Code Style Index
Better than Tom Kyte?
Good Day to Worse Day

Archives
August 2007 / September 2007 / October 2007 / November 2007 / December 2007 / January 2008 / February 2008 / March 2008 / April 2008 / May 2008 / June 2008 / July 2008 / August 2008 /


 

Powered by Blogger

Aggregated by OraNA