What Is INSERT ALL statement In Oracle?

The INSERT ALL statement in Oracle allows you to insert multiple rows into one or more tables in a single SQL statement. It is a shorthand way of performing multiple INSERT operations, and it can be used to insert data into different tables in one statement, based on conditions or fixed values.

Syntax of the INSERT ALL Statement

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

• INTO clause: Specifies the table and columns where the data will be inserted.
• VALUES clause: Specifies the values to be inserted into the respective columns.
• SELECT * FROM dual: The dual table is a special table in Oracle used when a SELECT statement is required but no actual table data is being selected.

Key Points:

• You can insert into multiple tables in a single INSERT ALL statement.
• The SELECT statement at the end of the query is required and is typically SELECT * FROM dual, which is a dummy query in Oracle used when a SELECT is needed but no table data is actually queried.
• Each INTO clause specifies an individual insert operation.

Example 1: Inserting into Multiple Tables

Suppose we have two tables: employees and departments.

• employees table:
  • emp_id (Primary Key)
  • emp_name
  • emp_salary
• departments table:
  • dept_id (Primary Key)
  • dept_name

Here’s how you can use INSERT ALL to insert data into both tables in one statement:

INSERT ALL
    INTO employees (emp_id, emp_name, emp_salary) VALUES (1, 'Alice', 5000)
    INTO employees (emp_id, emp_name, emp_salary) VALUES (2, 'Bob', 6000)
    INTO departments (dept_id, dept_name) VALUES (1, 'HR')
    INTO departments (dept_id, dept_name) VALUES (2, 'IT')
SELECT * FROM dual;

Explanation:

• First INTO clause: Inserts the first employee (Alice) into the employees table.
• Second INTO clause: Inserts the second employee (Bob) into the employees table.
• Third INTO clause: Inserts a department (HR) into the departments table.
• Fourth INTO clause: Inserts another department (IT) into the departments table.
• SELECT * FROM dual: This is a dummy select statement needed to execute the INSERT ALL.

Example 2: Using INSERT ALL with a SELECT Statement

You can also use INSERT ALL with a SELECT statement to conditionally insert data. This is particularly useful when you’re inserting data based on the result of a query.

Example:

Let’s say you have a table called products and another table discounted_products. You want to insert all products with a price greater than 100 into both the products table and the discounted_products table.

INSERT ALL
    INTO products (product_id, product_name, product_price)
        SELECT product_id, product_name, product_price
        FROM products_data
        WHERE product_price > 100
    INTO discounted_products (product_id, product_name, product_price)
        SELECT product_id, product_name, product_price
        FROM products_data
        WHERE product_price > 100
SELECT * FROM dual;

Explanation:

• This query inserts all products with a price greater than 100 from the products_data table into both the products and discounted_products tables.
• The SELECT statement retrieves the data, and INSERT ALL inserts the data into both tables.

Example 3: Conditional Insertions with WHEN Clause

You can also add a WHEN clause to perform conditional insertions based on certain conditions.

INSERT ALL
    INTO employees (emp_id, emp_name, emp_salary) 
        VALUES (1, 'John Doe', 5000)
    INTO employees (emp_id, emp_name, emp_salary) 
        VALUES (2, 'Jane Smith', 6000)
    INTO departments (dept_id, dept_name)
        VALUES (1, 'Marketing')
    WHEN (SELECT COUNT(*) FROM employees WHERE emp_salary > 5500) > 0
    INTO departments (dept_id, dept_name)
        VALUES (2, 'Finance')
SELECT * FROM dual;

Explanation:

• The WHEN clause checks a condition (in this case, whether there is any employee with a salary greater than 5500). If the condition is met, the second INTO clause for the departments table is executed.
• In this example, if there are employees with a salary greater than 5500, it will insert a department with the name “Finance”; otherwise, it won’t insert anything into the departments table.

Advantages of Using INSERT ALL:

• Efficiency: You can insert data into multiple tables in a single operation, reducing the number of database round-trips.
• Simplicity: It simplifies the syntax when you need to insert similar data into different tables.

Limitations:

• All INTO clauses in an INSERT ALL statement must reference the same SELECT statement.
• INSERT ALL is often used for bulk inserts, but it can sometimes be less flexible than using individual INSERT statements, especially when complex logic or transactions are involved.

Summary:

The INSERT ALL statement in Oracle is a powerful tool for inserting data into multiple tables in one SQL statement. It provides a concise way to perform multiple insertions, making it easier to handle bulk inserts in some cases. You can use it with multiple INTO clauses, each specifying different target tables, and optionally combine it with conditions using the WHEN clause for more control.