Techblogs

Table values constructor

Terug naar overzicht

1 december 2023

When you create scripts to insert data, for example, when creating demos or filling up tables with default data, you always find yourself repeating the insert statement and possibly the column list for every row you want to add.

To illustrate this, let's create two tables, one for constructors and one for drivers:

create table if not exists constructors
(
name          varchar2( 16 )
, nationality   varchar2( 16 )
)
/
create table if not exists drivers
(
driver_number number ( 2 )
, code          varchar2( 3 )
, driver_name   varchar2( 32 )
, dob           date
, nationality   varchar2( 16 )
)
/

If you want to add the constructors for this year (2023) you have two choices. Write a separate statement for every row to be added:

insert into constructors ( name, nationality ) values ( 'Alfa Romeo'    , 'Swiss'    );
insert into constructors ( name, nationality ) values ( 'AlphaTauri'    , 'Italian' );
insert into constructors ( name, nationality ) values ( 'Alpine F1 Team', 'French'   );
insert into constructors ( name, nationality ) values ( 'Aston Martin' , 'British' );

That is a lot of typing. Also,each statement is executed separately, resulting in feedback for each successful or failed operation. To minimize the feedback, you can wrap the insert statements in an anonymous block, which reduces execution time but increases the amount of typing:

begin
insert into constructors ( name, nationality ) values ( 'Ferrari'       , 'Italian' );
insert into constructors ( name, nationality ) values ( 'Haas F1 Team' , 'American' );
insert into constructors ( name, nationality ) values ( 'McLaren'       , 'British' );
insert into constructors ( name, nationality ) values ( 'Mercedes'      , 'German'   );
insert into constructors ( name, nationality ) values ( 'Red Bull'      , 'Austrian' );
insert into constructors ( name, nationality ) values ( 'Williams'      , 'British' );
end;
/

Oracle Database 23c introduces the Multi Row Insert option. You specify the insert statement once and provide multiple sets of row data:

insert into drivers
( driver_number, code, driver_name, dob, nationality )
values
( 44, 'HAM', 'Lewis Hamilton' , to_date( '19850107', 'YYYYMMDD' ), 'British'    )
, ( 14, 'ALO', 'Fernando Alonso' , to_date( '19810729', 'YYYYMMDD' ), 'Spanish'    )
, ( 10, 'GAS', 'Pierre Gasly'    , to_date( '19960207', 'YYYYMMDD' ), 'French'     )
, ( 27, 'HUL', 'Nico Hülkenberg' , to_date( '19870819', 'YYYYMMDD' ), 'German'     )
, ( 11, 'PER', 'Sergio Pérez'    , to_date( '19900126', 'YYYYMMDD' ), 'Mexican'    )
, ( 77, 'BOT', 'Valtteri Bottas' , to_date( '19890828', 'YYYYMMDD' ), 'Finnish'    )
, ( 20, 'MAG', 'Kevin Magnussen' , to_date( '19921005', 'YYYYMMDD' ), 'Danish'     )
, ( 33, 'VER', 'Max Verstappen' , to_date( '19970930', 'YYYYMMDD' ), 'Dutch'      )
, ( 55, 'SAI', 'Carlos Sainz'    , to_date( '19940901', 'YYYYMMDD' ), 'Spanish'    )
, ( 31, 'OCO', 'Esteban Ocon'    , to_date( '19960917', 'YYYYMMDD' ), 'French'     )
, ( 18, 'STR', 'Lance Stroll'    , to_date( '19981029', 'YYYYMMDD' ), 'Canadian'   )
, ( 16, 'LEC', 'Charles Leclerc' , to_date( '19971016', 'YYYYMMDD' ), 'Monegasque' )
, ( 4, 'NOR', 'Lando Norris'    , to_date( '19991113', 'YYYYMMDD' ), 'British'    )
, ( 63, 'RUS', 'George Russell' , to_date( '19980215', 'YYYYMMDD' ), 'British'    )
, ( 23, 'ALB', 'Alexander Albon' , to_date( '19960323', 'YYYYMMDD' ), 'Thai'       )
, ( 22, 'TSU', 'Yuki Tsunoda'    , to_date( '20000511', 'YYYYMMDD' ), 'Japanese'   )
, ( 24, 'ZHO', 'Guanyu Zhou'     , to_date( '19990530', 'YYYYMMDD' ), 'Chinese'    )
, ( 21, 'DEV', 'Nyck de Vries'   , to_date( '19950206', 'YYYYMMDD' ), 'Dutch'      )
, ( 81, 'PIA', 'Oscar Piastri'   , to_date( '20010406', 'YYYYMMDD' ), 'Australian' )
, ( 2, 'SAR', 'Logan Sargeant' , to_date( '20001231', 'YYYYMMDD' ), 'American'   )
/

Note, this is the driver list at the beginning of season 2023 and does not yet reflect the driver changes throughout the season.

The list of data can be arbitrarily long, but be aware that if one row fails, the entire statement fails. Luckily, the log errors construct also works with this new functionality, allowing you to handle errors efficiently. This functionality can also be used when generating the statement.

You can also use this construct in PL/SQL. However, in my opinion, a better approach would be to populate a collection and use it in a FORALL statement.

The source code used in this article can be found at:
https://github.com/Qualogy-Solutions/OracleDatabase23c/blob/main/tablevaluesconstructor.sql