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.