I have been using the INSERT ALL statement for a long time. But today I noticed that this was a totally new feature for a colleague of mine. Nothing strange really since Oracle have so many features. This is one of the reasons for writing this post.

So insert all ?

With just one SQL statement you can insert multiple rows into multiple tables. That statement can also be conditional or unconditional.

The syntax from the Oracle documentation is as follows

  { insert_into_clause [ values_clause ] [error_logging_clause] }...
| conditional_insert_clause
} subquery

Let’s start with an example.

create table all_emojis(smiley_name varchar2(100) primary key,tale varchar2(200));

Insert multiple rows into all_emojis table unconditional.

insert all
into all_emojis(smiley_name,tale)
values('Grinning','A yellow face grinning')

into all_emojis(smiley_name,tale)
values('Smiling','A yellow face with a modest smile, rosy cheeks')

into all_emojis(smiley_name,tale)
values('Disappointed','A yellow face with a frown and closed, downcast eyes')

into all_emojis(smiley_name,tale)
values('Angry','A red face with a frowning mouth and eyes and eyebrows scrunched downward in anger')
select 1 from dual;

Next example will do a conditional insert into two tables. I want my split my emojis into two tables.

create table emojis_happy(smiley_name varchar2(100) primary key,tale varchar2(200));
create table emojis_unhappy(smiley_name varchar2(100) primary key,tale varchar2(200));

Let’s push the happy and unhappy smileys into these new tables.

insert all
when smiley_name in('Grinning','Smiling') then
into  emojis_happy(smiley_name,tale)values(smiley_name,tale)
when smiley_name in('Disappointed','Angry') then
into emojis_unhappy(smiley_name,tale)values(smiley_name,tale)
select smiley_name,tale from all_emojis;

Verify that the data has been inserted properly.

select 'emojis_happy' tblname, a.* from emojis_happy a
select 'emojis_unhappy' tblname, b.* from emojis_unhappy b;
TBLNAME        SMILEY_NAME       TALE                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
emojis_happy   Grinning         A yellow face grinning                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
emojis_happy   Smiling          A yellow face with a modes...                                                                                                                                                                                                                                                                                                                                                                                                                                                                     
emojis_unhappy Angry            A red face with a frowning....                                                                                                                                                                                                                                                                                                                                                                                                                 
emojis_unhappy Disappointed     A yellow face with a frown....

So what about that error logging clause? Let’s try to insert a duplicate row into the all_emojis table. The smiley_name is the primary key of the table.

insert all
into all_emojis(smiley_name,tale)values('Grinning','A yellow face grinning')
select 1 from dual;

ORA-00001: unique constraint (SYS_C0029768) violated

One can use the error clause to suppress or log the error message. One example of this would be.

insert all
into all_emojis(smiley_name,tale)values('Grinning','A yellow face grinning')log errors reject limit unlimited
select 1 from dual;

But to make that work, one needs to set up a proper DML error logging table. The DBMS_ERRLOG package provides a procedure that enables you to create an error logging table so that DML operations can continue after encountering errors rather than abort and roll back.