INSERT multiple rows with single SELECT statement - INSERT ALL

 
We might face the requirement to INSERT multiple records in a table; where we have either go for SQL Loader, CSV upload  or  Multiple INSERT statements. 

We have another simple option in ORACLE;  which is INSERT ALL..


Consider the table as example. 

TABLE NAME : DOMAIN

 Name             Type
 ---------------- ------------
 DOMAIN_NAME    VARCHAR2(30)
 REGION              VARCHAR2(10)

Consider the requirement to INSERT the below data 

DOMAIN_NAME       REGION
---------------- --------
D001                   APAC
D002                   EMEA
D003                   US
D004                   APAC
D005                   US

Using INSERT statements:-

INSERT into DOMAIN (DOMAIN_NAME, REGION) values ('D001', 'APAC' );

COMMIT;

INSERT into DOMAIN (DOMAIN_NAME, REGION) values ('D002', 'EMEA' );

COMMIT;

 

INSERT into DOMAIN (DOMAIN_NAME, REGION) values ('D003', 'US' );

COMMIT;

 

INSERT into DOMAIN (DOMAIN_NAME, REGION) values ('D004', 'APAC' );

COMMIT;

INSERT into DOMAIN (DOMAIN_NAME, REGION) values ('D005', 'US' );

COMMIT;

Think about if this requirement is for inserting more number of records. 

We need to write and execute "N" number of INSERT statements.

GOOD NEWS IS.. we can achieve the same by using single INSERT statement.

Using INSERT ALL statement:-

INSERT ALL 

into DOMAIN(DOMAIN_NAME, REGION) values('D001', 'APAC' ) 

into DOMAIN(DOMAIN_NAME, REGION) values('D002', 'EMEA' ) 

into DOMAIN(DOMAIN_NAME, REGION) values('D003', 'US' ) 

into DOMAIN(DOMAIN_NAME, REGION) values('D004', 'APAC' ) 

into DOMAIN(DOMAIN_NAME, REGION) values('D005', 'US' ) ;

COMMIT;

With respect to Performance; INSERT ALL is better, because parsing iteration to the table DOMAIN takes place for one time; whereas for INSERT statement, parsing takes place for multiple times.