INSERT ALL: Insert Multiple Rows with a Single INSERT Statement

A quick tip to get rid of the plodding way of inserting data into a table with multiple INSERT statements. Know what I mean? Say you have a table COLORS with this structure:

 Name             Type
 ---------------- ------------
 NAME             VARCHAR2(30)
 CATEGORY         VARCHAR2(10) 

And you want to create this data in the table COLORS:

NAME                           CATEGORY
------------------------------ --------
yellow                         1
red                            1
blue                           1
yellow                         2
blue                           2

Hands up all who write insert statements this way:

insert into colors (name, category)
values ('yellow', 1);

insert into colors (name, category)
values ('red', 1);

insert into colors (name, category)
values ('blue', 1);

insert into colors (name, category)
values ('yellow', 2);

insert into colors (name, category)
values ('blue', 2);

Good news for you — you can condense multiple INSERTS into a single SQL command with INSERT ALL.

INSERT ALL – the shorthand multi-table INSERT

This is how you’d do it:

insert all
into colors(name, category) values('yellow', 1)
into colors(name, category) values('red', 1)
into colors(name, category) values('blue', 1)
into colors(name, category) values('yellow', 2)
into colors(name, category) values('blue', 2)
select * from dual;

INSERT and INSERT ALL in action

A demo run to show identical results with the two styles:

Without INSERT ALL:

SQL> -- Without INSERT ALL
SQL> insert into colors (name, category)
  2  values ('yellow', 1);

1 row created.

SQL> insert into colors (name, category)
  2  values ('red', 1);

1 row created.

SQL> insert into colors (name, category)
  2  values ('blue', 1);

1 row created.

SQL> insert into colors (name, category)
  2  values ('yellow', 2);

1 row created.

SQL> insert into colors (name, category)
  2  values ('blue', 2);

1 row created.

SQL> select * from colors;

NAME                           CATEGORY
------------------------------ ----------
yellow                         1
red                            1
blue                           1
yellow                         2
blue                           2

With INSERT ALL:

SQL> -- With INSERT ALL
SQL> insert all
  2  into colors(name, category) values('yellow', 1)
  3  into colors(name, category) values('red', 1)
  4  into colors(name, category) values('blue', 1)
  5  into colors(name, category) values('yellow', 2)
  6  into colors(name, category) values('blue', 2)
  7  select * from dual;

5 rows created.

SQL> select * from colors;

NAME                           CATEGORY
------------------------------ ----------
yellow                         1
red                            1
blue                           1
yellow                         2
blue                           2

For Further Reading

A few similar DML constructs that make processing simpler:

• UNPIVOT to Select Columns as Rows
• Flatten Hierarchical Data using a Single SQL
• LISTAGG to Convert Rows to Comma-Separated String