Oracle INSERT ALL Statement

In Oracle, INSERT ALL statement allows you to insert multiple rows with a single INSERT statement. You can insert the rows into one table or multiple tables by using only one SQL command.

Oracle INSERT ALL Syntax

To insert multiple rows with a single INSERT statement, use the following syntax:

Syntax

INSERT ALL
INTO table_name (col1, col2, col_n) VALUES (expr1, expr2, expr_n)  
INTO table_name(col1, col2, col_n) VALUES (expr1, expr2, expr_n) 
SELECT * FROM dual;

Parameters:

table_name: The table name to insert the records into.

col1: The columns in the table to insert values.

expr1: The values to assign to the columns in the table.

Oracle INSERT ALL example

The following Oracle statement to insert all the field values with single insert statement.

Example

INSERT ALL
  INTO tblCustomerLoan (firstName, lastName) VALUES ('Ajay', 'Joshi')
  INTO tblCustomerLoan (firstName, lastName) VALUES ('Vijay', 'Malhotra')
  INTO tblCustomerLoan (firstName, lastName) VALUES ('Dev', 'Malik')
SELECT * FROM dual;

Note:

In the above example, we have inserted multiple records from "tblCustomerLoan" using single INSERT statement.

« Previous