Insert Multiple Rows Oracle

Inserting multiple rows into an Oracle database can be an efficient way to populate tables with data. This operation is commonly used in various scenarios, such as data migration, batch processing, or initializing a new database. In Oracle, you can insert multiple rows using a single SQL statement, which can significantly improve performance compared to executing multiple separate INSERT statements.

Using the INSERT ALL Statement

The INSERT ALL statement is a powerful tool in Oracle that allows you to insert multiple rows into one or more tables in a single operation. The basic syntax of the INSERT ALL statement is as follows:

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

In this syntax, you can specify multiple INTO clauses to insert data into different tables. However, if you are inserting data into a single table, you can use the INTO clause multiple times with different VALUES clauses to insert multiple rows.

Example of Inserting Multiple Rows into a Single Table

Let’s consider an example where we have a table named “employees” with columns “employee_id”, “name”, and “department”. We want to insert multiple rows into this table using the INSERT ALL statement.

INSERT ALL
  INTO employees (employee_id, name, department)
  VALUES (1, 'John Doe', 'Sales')
  INTO employees (employee_id, name, department)
  VALUES (2, 'Jane Smith', 'Marketing')
  INTO employees (employee_id, name, department)
  VALUES (3, 'Bob Johnson', 'IT')
SELECT * FROM dual;

This statement will insert three new rows into the "employees" table with the specified values.

Using the INSERT INTO Statement with SELECT

Another way to insert multiple rows into an Oracle table is by using the INSERT INTO statement combined with a SELECT statement. This method is particularly useful when you need to insert data from one table into another.

The basic syntax for this method is:

INSERT INTO table_name (column1, column2, column3,...)
SELECT column1, column2, column3,...
FROM source_table;

This statement inserts rows into "table_name" based on the rows selected from "source_table". The number and data types of the columns in the INSERT INTO clause must match those in the SELECT clause.

Example of Inserting Multiple Rows Using INSERT INTO with SELECT

Consider an example where we want to insert all employees from the “new_employees” table into the “employees” table.

INSERT INTO employees (employee_id, name, department)
SELECT employee_id, name, department
FROM new_employees;

This statement will insert all rows from the "new_employees" table into the "employees" table, provided that the structures of the two tables are compatible.

Best Practices for Inserting Multiple Rows

When inserting multiple rows into an Oracle database, consider the following best practices:

• Use Bulk Operations: For large datasets, using bulk operations like INSERT ALL can improve performance.
• Commit Regularly: When inserting a large number of rows, commit the changes regularly to avoid transaction log overflow and to ensure data consistency.
• Check Constraints: Before inserting data, ensure that it complies with the constraints defined on the target table, such as primary keys, unique constraints, and check constraints.
• Monitor Performance: Keep an eye on system resources and query performance. Adjust your insertion strategy as needed to avoid impacting other database operations.

By following these guidelines and examples, you can efficiently insert multiple rows into your Oracle database, ensuring data integrity and optimal performance.