INSERT ALL: Insert Multiple Rows with a Single INSERT Statement

SEPTEMBER 18, 2012

in KEYWORDS,SQL

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:

1 2 3 4

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

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

1 2 3 4 5 6 7

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

Hands up all who write insert statements this way:

1 2 3 4 5 6 7 8 9 10 11 12 13 14

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:

1 2 3 4 5 6 7

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:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35

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:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

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

Related Posts: