INSERT ALL in Oracle

by

ORACLE INSERT ALL
To insert multiple rows, the Oracle INSERT ALL statement is used. A single INSERT statement, as a SQL command thus can insert the rows into one table or multiple tables.

Syntax: 

INSERT ALL  
INTO table_name (column_1, column_2, column_n) 
VALUES (expression_1, expression_2, ... expression_n)  
INTO table_name(column_1, column_2, column_n) 
VALUES (expression_1, expression_2, ... expression_n)  
INTO table_name (column_1, column_2, column_n) 
VALUES (expression_1, expression_2, ... expression_n)  
Subquery;

Parameters:
table_name: It is used to specify the name of the table in which the records need to be inserted.
column_1, column_2, … column_n: It is used to specify the columns of the table in which the values needs to be inserted.
expression_1, expression_2, … expression_n: It is used to specify the values to be inserted to the respective columns. For example, expression_1 will be inserted into column_1, expression_2 to column_2 and so on.
subquery: A selected list of the subquery returns a column to which each value expression must refer to. The subquery- SELECT * FROM dual; -is used to get the literal values and not the values returned by the subquery.

Example: Inserting multiple rows into a single table. 

INSERT ALL  
NTO students(student_id, student_name, student_age)  
VALUES  (3, 'Happy’, 11);  
INTO students(student_id, student_name, student_age)  
VALUES  (2, 'Smiley’, 13);  
INTO students(student_id, student_name, student_age)  
VALUES  (1, 'Joy’, 9);  
SELECT * FROM dual;

Output: 

3 row(s) inserted.
0.01 seconds

Explanation:
The ‘students’ is an already existing table in which we are inserting three rows by a single SQL command.

Example: Inserting multiple rows into multiple tables. 

INSERT ALL  
INTO students(student_id, student_name, student_age)  
VALUES  (3, 'Happy’, 11);  
INTO students(student_id, student_name, student_age)  
VALUES  (2, 'Smiley’, 13);  
INTO students(student_id, student_name, student_age)  
VALUES  (1, 'Joy’, 9);
INTO teachers (id, name, age) VALUES (222, 'Smith', 30)  
SELECT * FROM dual;

Output: 

4 row(s) inserted.
0.03 seconds

Explanation:
The ‘students’ and the ‘teachers’ are two already existing tables. We are inserting three rows into the first table and 0ne row in the second table by a single SQL command.