SQL INSERT ALL

In this tutorial, we will learn how to use INSERT ALL statement. It will help us in order to enter multiple rows of a table into a single table or multiple tables.

As we have entered the data previously, we will see to it.

Select * from users;

Output:

In this we can see, UserID, Username, Age, Email, but there is not even a single record which have inserted. So I wanted to insert multiple rows at a time.

Previously we have inserted a single row, but now will see inserting multiple rows at a time.

Syntax:

INSERT ALL 
INTO table_name(col1,col2,col3) VALUES(val1,val2, val3) 
INTO table_name(col1,col2,col3) VALUES(val4,val5, val6) 
INTO table_name(col1,col2,col3) VALUES(val7,val8, val9) 
Subquery;

Example:

insert all 
into users(user_id, user_name, age, email) values ( 1, 'hari', 23, 'hari@gmail.com')
into users(user_id, user_name, age, email) values ( 2, 'ravi', 24, 'ravi@gmail.com')
into users(user_id, user_name, age, email) values ( 3, 'Sasi', 25, 'sasi@gmail.com')
into users(user_id, user_name, age, email) values ( 4, 'Sam', 26, 'sam@gmail.com')
into users(user_id, user_name, age, email) values ( 5, 'lisa', 27, 'lisa@gmail.com')
Select * from dual;

Commit;

Select * from users;

Output:

There is another method where you can insert the data to the existing table.

Let’s see the example below.

Example:

create table test ( id number, name varchar2(20))

select * from test;

Output:

Where you can see two fields have been updated with ID and Name. This time I would like to insert two rows in Users table and two rows in test table.

Example:

insert all 
into users(user_id, user_name, age, email) values ( 6, 'sita', 23, 'sita@gmail.com')
into users(user_id, user_name, age, email) values ( 7, 'gita', 24, 'gita@gmail.com')
into test(id, name) values ( 1, 'Sasi')
into test(id, name) values ( 2, 'Sam')
select * from dual;