Using INSERT ALL Statement in Oracle

This article is written about how to use the Oracle INSERT ALL announcement with syntax and examples.

Description

The Oracle INSERT ALL assertion is used to add a couple of rows with a single INSERT statement. The rows can be inserted into one desk or multiple tables the usage of only one SQL command.

Syntax

The syntax for the INSERT ALL assertion in Oracle/PLSQL is:

INSERT ALL
  INTO mytable (column1, column2, column_n) VALUES (expr1, expr2, expr_n)
  INTO mytable (column1, column2, column_n) VALUES (expr1, expr2, expr_n)
  INTO mytable (column1, column2, column_n) VALUES (expr1, expr2, expr_n)
SELECT * FROM dual;

Parameters or Arguments

mytable The desk to insert the documents into. column1, column2, column_n The columns in the table to insert values. expr1, expr2, … expr_n The values to assign to the columns in the table.

Example – Insert into One Table

You can use the INSERT INTO statement to insert a couple of documents into one table.

For example, if you wanted to insert three rows into the suppliers table, you could run the following SQL statement:

INSERT ALL
  INTO suppliers (supplier_id, supplier_name) VALUES (1000, 'IBM')
  INTO suppliers (supplier_id, supplier_name) VALUES (2000, 'Microsoft')
  INTO suppliers (supplier_id, supplier_name) VALUES (3000, 'Google')
SELECT * FROM dual;

This is equal to the following three INSERT statements:

INSERT INTO suppliers (supplier_id, supplier_name) VALUES (1000, 'IBM');

INSERT INTO suppliers (supplier_id, supplier_name) VALUES (2000, 'Microsoft');

INSERT INTO suppliers (supplier_id, supplier_name) VALUES (3000, 'Google');

Example – Insert into Multiple Tables

You can additionally use the INSERT ALL assertion to insert a couple of rows into extra than one table in one command.

For example, if you desired to insert into each the suppliers and clients table, you should run the following SQL statement:

INSERT ALL
  INTO suppliers (supplier_id, supplier_name) VALUES (1000, 'IBM')
  INTO suppliers (supplier_id, supplier_name) VALUES (2000, 'Microsoft')
  INTO customers (customer_id, customer_name, city) VALUES (999999, 'Anderson Construction', 'New York')
SELECT * FROM dual;

This example will insert 2 rows into the suppliers desk and 1 row into the customers table. It is equivalent to walking these three INSERT statements:

INSERT INTO suppliers (supplier_id, supplier_name) VALUES (1000, 'IBM');

INSERT INTO suppliers (supplier_id, supplier_name) VALUES (2000, 'Microsoft');

INSERT INTO customers (customer_id, customer_name, city) VALUES (999999, 'Anderson Construction', 'New York');