Generating Records from DUAL

How meny times You need to generate a test with a million of rows os some exaxt number of rows,


A trick that a friend told me is, 

Just use Dual,


select *
  from dual
connect by level <=:p_number_of_rows;

With this you have all the rows that you could need whithout a single insert.

Hope it helps.

Mutli Table Inserts

How many times you have faced the Issue when loading information that your query resolved more than one issue but then you want to insert the results on more than one table,
Normally You will create something like this

INSERT into TABLE A
Values ..
Query

INSERT into TABLEB
Values ..
Query

INSERT into TABLEC
Values ..
Query


For those cases oracle created the Multi Table Insert which loks like this:

INSERT
WHEN ... THEN
INTO ... VALUES ...
WHEN ... THEN
INTO ... VALUES ...
subquery;


Aa simple sample will be if you want to insert the firsr 5 row's on the table A, then on the next 2 records on table b and then everithing else on other table, the sample will look like this:

I created this 3 tables:

  create table table_demo_1
  (dat_1 varchar2(10),
   num_2 number  );
 
  create table table_demo_2
  (dat_1 varchar2(10),
   num_2 number  );
  
   create table table_demo_3
  (dat_1 varchar2(10),
   num_2 number  );

  
And onse done that I execute the sentence has follows

INSERT
WHEN ROW_NUM<=5 THEN
INTO table_demo_1 (dat_1,NUM_2) VALUES ('A',ROW_NUM)
WHEN ROW_NUM BETWEEN 6 AND 7 THEN
INTO table_demo_2 (dat_1,NUM_2) VALUES ('B',ROW_NUM)
ELSE
INTO table_demo_3 (dat_1,NUM_2) VALUES ('C',ROW_NUM)
  SELECT DUMMY, ROWNUM ROW_NUM
      FROM dual
     CONNECT BY LEVEL<=10 ;


Easy has you can see.