INSERT: adding rows

Part 9 changes data, so every page works on its own scratch copies (tables starting D9), never on the cricket tables themselves. Each way of doing the job gets its own copy, and the tests check that every copy ends up the same.

The test case

The league is expanding for 2027: add the Lagos Lanterns (LGS, team 7, Lagos, Nigeria) and the Vancouver Voyagers (VAN, team 8, Vancouver, Canada). Neither has a home ground yet.

Each way starts from a copy of the six teams:

Setup

begin
  for t in (select table_name from user_tables
            where  table_name in ('D90_TEAMS_A', 'D90_TEAMS_B', 'D90_TEAMS_C', 'D90_TEAMS_D')) loop
    execute immediate 'drop table ' || t.table_name || ' purge';
  end loop;
end;
/

Setup

create table d90_teams_a as select * from cr_teams;

Setup

create table d90_teams_b as select * from cr_teams;

Setup

create table d90_teams_c as select * from cr_teams;

Setup

create table d90_teams_d as select * from cr_teams;

Expected result

All eight teams, from whichever copy:

Eight teams, two new continents. The fixture computer is already sulking.

Way 1: one INSERT ... VALUES per row

Setup

insert into d90_teams_a (team_id, team_code, team_name, city, country)
values (7, 'LGS', 'Lagos Lanterns', 'Lagos', 'Nigeria');

Setup

insert into d90_teams_a (team_id, team_code, team_name, city, country)
values (8, 'VAN', 'Vancouver Voyagers', 'Vancouver', 'Canada');

select team_id, team_code, team_name, city, country, home_venue_id
from   d90_teams_a order by team_id;

• Always list the columns. insert into t values (...) without a column list breaks the day someone adds a column, and silently swaps values if two columns have the same type.
• Columns left out (home_venue_id) get their default, here null.

Way 2: INSERT ALL, several rows in one statement

Setup

insert all
  into d90_teams_b (team_id, team_code, team_name, city, country) values (7, 'LGS', 'Lagos Lanterns', 'Lagos', 'Nigeria')
  into d90_teams_b (team_id, team_code, team_name, city, country) values (8, 'VAN', 'Vancouver Voyagers', 'Vancouver', 'Canada')
select * from dual;

select team_id, team_code, team_name, city, country, home_venue_id
from   d90_teams_b order by team_id;

The classic 19c trick for several rows at once. The SELECT * FROM dual at the end is required: INSERT ALL inserts once per row of that query, and DUAL has exactly one. (Page 9.1 has the real power of INSERT ALL.)

Way 3: INSERT ... SELECT

Setup

insert into d90_teams_c (team_id, team_code, team_name, city, country)
select 7, 'LGS', 'Lagos Lanterns', 'Lagos', 'Nigeria' from dual union all
select 8, 'VAN', 'Vancouver Voyagers', 'Vancouver', 'Canada' from dual;

select team_id, team_code, team_name, city, country, home_venue_id
from   d90_teams_c order by team_id;

INSERT ... SELECT is the workhorse: anything a query can produce, you can insert. Here the query is two made-up rows; usually it's a join over other tables.

Way 4: several rows in one VALUES (26ai)

Setup

insert into d90_teams_d (team_id, team_code, team_name, city, country)
values (7, 'LGS', 'Lagos Lanterns', 'Lagos', 'Nigeria'),
       (8, 'VAN', 'Vancouver Voyagers', 'Vancouver', 'Canada');

26ai only

select team_id, team_code, team_name, city, country, home_venue_id
from   d90_teams_d order by team_id;

What every other database had for years. Oracle finally caught up in 23ai.

Getting values back: RETURNING

In PL/SQL, RETURNING hands back values from the row just inserted (handy when an identity column or trigger sets the key):

declare
  l_id number;
begin
  insert into d90_teams_a (team_id, team_code, team_name, city, country)
  values (9, 'NBO', 'Nairobi Nightjars', 'Nairobi', 'Kenya')
  returning team_id into l_id;
  dbms_output.put_line('new team ' || l_id);
  rollback;
end;
/

Transactions: COMMIT, ROLLBACK, SAVEPOINT

Changes are private to your session until you COMMIT. Until then you can undo them:

insert into d90_teams_a (team_id, team_code, team_name, city, country) values (9, 'NBO', 'Nairobi Nightjars', 'Nairobi', 'Kenya');
savepoint after_nairobi;
delete from d90_teams_a where team_id = 1;   -- oops, deleted Tampa
rollback to after_nairobi;                   -- Tampa is back; Nairobi is still there
rollback;                                    -- and now Nairobi is gone too

Any DDL (CREATE, ALTER, DROP, TRUNCATE) commits first, whether you meant to or not.

19c and 26ai

Ways 1-3 on both; Way 4 needs 26ai.

Gotchas

• A forgotten COMMIT. Your session sees the new rows; nobody else does. Close the tool without committing and (depending on the tool) they're gone.
• Column order in INSERT ... SELECT is by position, not by name. Line them up carefully.