Insert Multiple Rows Oracle: INSERT ALL Guide

1 year ago
Liam
1 minute

One option could be:
To simultaneously add multiple lines of data to an Oracle database, you can use the INSERT ALL statement.

The INSERT ALL statement allows for the insertion of multiple rows into one or more tables at once. Here is the basic syntax for the INSERT ALL statement:

INSERT ALL
  INTO table_name (column1, column2, ...)
    VALUES (value1, value2, ...)
  INTO table_name (column1, column2, ...)
    VALUES (value1, value2, ...)
  ...
SELECT * FROM dual;

In this case, table_name refers to the table where data will be inserted, column1, column2, etc. are the column names where data will be inserted, and value1, value2, etc. are the specific values to be inserted.

Here is a specific example:

INSERT ALL
  INTO employees (employee_id, first_name, last_name)
    VALUES (1001, 'John', 'Doe')
  INTO employees (employee_id, first_name, last_name)
    VALUES (1002, 'Jane', 'Smith')
  INTO employees (employee_id, first_name, last_name)
    VALUES (1003, 'Mike', 'Johnson')
SELECT * FROM dual;

This example will insert three rows of data into the employees table at the same time.

Please note that all INSERT statements must end with SELECT * FROM dual; This is a specific requirement of Oracle.

bannerAds