INSERT ALL with single sequence

If you want to perform an insert into two or more tables using an INSERT ALL
and also set the primary key to a unique number for each table based
on a sequence, then the following is a method that could be used.

For example;

Create some test tables first;

create table t1 (a number primary key, b number);
create table t2 (a number primary key, b number);

Create the sequence next.

The trick is to create the sequence with an increment value equal to
the number of tables being inserted into. So if you have three tables
then the increment value would be three. In the example we have two tables
so the increment value is 2.

create sequence test_seq increment by 2 minvalue 0;

Now for the test;

insert all
when 1 = 1 then
  into t1 (a, b) values (test_seq.nextval, 1)
when 1 = 1 then
  into t2 (a, b) values (test_seq.nextval  - 1, 1)
select 1, 1 from dual;

SQL> select * from t1
  2  union all
  3  select * from t2
  4  order by 1;

         A          B
---------- ----------
         3          1
         4          1

One might want to wrap the insert in a procedure, since although the
trick works in versions 10g and 11g, future versions might not output
the same results. The INSERT ALL within the procedure would refer to
a number (which would be passed as a parameter) instead of the
sequence.

For example;

create or replace procedure p_Test (p_Number in number) as
  begin

    insert all
      when 1 = 1 then
      into t1 (a, b) values (p_Number, 1)
    when 1 = 1 then
      into t2 (a, b) values (p_Number  - 1, 1)
    select 1, 1 from dual;

end p_Test;
/

Now test using the procedure;

SQL> exec p_Test(test_seq.nextval);

PL/SQL procedure successfully completed.

SQL> select * from t1
  2  union all
  3  select * from t2
  4  order by 1;

         A          B
---------- ----------
         3          1
         4          1
         5          1
         6          1