Today, a friend asked me on QQ whether there is a way to merge two SQL like:

insert into table（a,b） values('aa','bb');
insert into table（a,b） values('cc','dd');

I think there's no meaning to do that. But there is a way to implement it. Is is a new feature in Oracle 9i:  Multitable Inserts.

There are two syntaxes:

1. Unconditional insert into ALL tables

INSERT ALL
  INTO sal_history VALUES(empid,hiredate,sal)
  INTO mgr_history VALUES(empid,mgr,sysdate)
SELECT employee_id EMPID, hire_date HIREDATE, salary SAL, manager_id MGR
  FROM employees WHERE employee_id > 200; 

2. Conditionally insert into ALL / First tables

INSERT ALL / FIRST
  WHEN SAL > 25000  THEN
    INTO special_sal VALUES(DEPTID,SAL)
  WHEN HIREDATE like ('%00%') THEN
    INTO hiredate_history_00 VALUES(DEPTID,HIREDATE)
  WHEN HIREDATE like ('%99%') THEN
    INTO hiredate_history_99 VALUES(DEPTID,HIREDATE)
  ELSE
    INTO hiredate_history VALUES(DEPTID, HIREDATE)
SELECT department_id DEPTID, SUM(salary) SAL,
    MAX(hire_date) HIREDATE
  FROM employees GROUP BY department_id;

Semantics:
ALL: insert into all the table.
        You can only use ALL keyword in Unconditional insert.
        If you use ALL keyword in Conditional insert, it means insert data into all the table that meet the condition.

FIRST:  If you use FIRST keyword in Conditional insert, it means insert data into the first table that meet the condition.

ELSE: If you use ELSE keyword, the row that don't meet any condition will be inserted into this table. Otherwise, the row will not be inserted into any table.

Now I'll give an example that resolve the issue above with multitable inserts:

STSC@SBOX> create table alltest (a varchar2(10), b varchar2(10));

Table created.

Elapsed: 00:00:00.00
STSC@SBOX> insert all
  2  into alltest values(a,b)
  3  into alltest values(c,d)
  4  select 'aa' a,'bb' b,'cc' c,'dd' d from dual;

2 rows created.

Elapsed: 00:00:00.00
STSC@SBOX> select * from alltest;

A                    B
-------------------- --------------------
aa                   bb
cc                   dd

2 rows selected.

Elapsed: 00:00:00.00 

It's easy!!

Reference: SQL New Features In Oracle9i