INSERT FIRST and INSERT ALL: one query, several tables

A conditional multi-table insert reads its source once and sends each row to one or more tables:

• INSERT ALL WHEN ... INTO ... puts a row into every table whose condition it meets.
• INSERT FIRST WHEN ... INTO ... puts it into the first table whose condition it meets, like a CASE.

The test case

File every delivery of the 2026 final (match 32) into one of three tables: wickets, boundaries (a four or six off the bat) and everything else. A wicket ball goes in wickets only.

Setup

begin
  for t in (select table_name from user_tables where table_name like 'D91\_%' escape '\') loop
    execute immediate 'drop table ' || t.table_name || ' purge';
  end loop;
end;
/

Setup

begin
  for s in (select column_value as suffix from table(sys.odcivarchar2list('F', 'S'))) loop
    for n in (select column_value as name from table(sys.odcivarchar2list('WKTS', 'BOUNDS', 'REST'))) loop
      execute immediate 'create table d91_' || n.name || '_' || s.suffix ||
                        ' (innings_no number, over_ball varchar2(5), batter_id number, runs number)';
    end loop;
  end loop;
end;
/

Six empty tables: D91_WKTS_F, D91_BOUNDS_F, D91_REST_F for Way 1 and the same with _S for Way 2. (Yes, that block creates tables in a loop. DBAs, look away.)

Expected result

The 2026 final, filed: 14 wickets (10 of Chennai's, 4 of Leeds's), 44 boundaries worth 204 runs, and 164 other balls that scored only 119 between them. Boundaries win finals.

Way 1: INSERT FIRST

Setup

insert first
  when is_wicket = 1                  then into d91_wkts_f   values (innings_no, over_ball, batter_id, total_runs)
  when runs_batter in (4, 6)          then into d91_bounds_f values (innings_no, over_ball, batter_id, total_runs)
  else                                     into d91_rest_f   values (innings_no, over_ball, batter_id, total_runs)
select innings_no, over_ball, batter_id, runs_batter, total_runs, is_wicket
from   cr_balls_v
where  match_id = 32;

select 'WICKETS' as tbl, count(*) as balls, sum(runs) as runs from d91_wkts_f   union all
select 'BOUNDARIES',     count(*),          sum(runs)         from d91_bounds_f union all
select 'REST',           count(*),          sum(runs)         from d91_rest_f;

The conditions are checked in order, and the first true one wins, so a (rare) wicket off a ball the batter also hit for four would land in wickets only.

Way 2: three INSERT ... SELECT statements

Setup

insert into d91_wkts_s
select innings_no, over_ball, batter_id, total_runs from cr_balls_v
where  match_id = 32 and is_wicket = 1;

Setup

insert into d91_bounds_s
select innings_no, over_ball, batter_id, total_runs from cr_balls_v
where  match_id = 32 and is_wicket = 0 and runs_batter in (4, 6);

Setup

insert into d91_rest_s
select innings_no, over_ball, batter_id, total_runs from cr_balls_v
where  match_id = 32 and is_wicket = 0 and runs_batter not in (4, 6);

select 'WICKETS' as tbl, count(*) as balls, sum(runs) as runs from d91_wkts_s   union all
select 'BOUNDARIES',     count(*),          sum(runs)         from d91_bounds_s union all
select 'REST',           count(*),          sum(runs)         from d91_rest_s;

Three passes over the source, and each later condition has to repeat "and not the earlier ones". INSERT FIRST does that bookkeeping for you, in one pass.

INSERT ALL: every matching table

Change FIRST to ALL and a row goes into every table whose condition is true. Typical use: writing a fact row and an audit row in one go, or unpivoting into several tables.

insert all
  when runs_batter = 6 then into sixes_log  values (match_id, over_ball, batter_id)
  when 1 = 1           then into every_ball values (match_id, over_ball, batter_id, total_runs)
select match_id, over_ball, batter_id, runs_batter, total_runs from cr_balls_v where match_id = 32;

19c and 26ai

The same on both.

Gotchas

• ELSE belongs to INSERT FIRST and INSERT ALL WHEN, and catches rows no WHEN took.
• No sequences in the subquery. Use seq.nextval in the VALUES clauses instead (and it's evaluated once per source row, not per target).
• Watch nulls in conditions: if runs_batter were null, runs_batter not in (4, 6) would be unknown and Way 2 would lose the row; INSERT FIRST ... ELSE would still catch it.