In Oracle Database, inserting data is not limited to simple INSERT INTO ... VALUES statements!

Oracle provides a powerful feature called conditional multi-table insert, commonly referred to as INSERT ALL with WHEN clauses.

This feature allows you to insert data into one or more tables based on conditions, all in a single SQL statement — improving both performance and code clarity.

What is INSERT ALL ... WHEN?

INSERT ALL with WHEN clauses allows you to:

• Evaluate conditions for each row in a source query
• Insert rows into different tables depending on those conditions
• Perform multiple inserts in a single pass over the data

This is especially useful in ETL processes, data transformation, and reporting pipelines.

Basic Syntax

INSERT ALL
   WHEN condition1 THEN
      INTO table1 (col1, col2) VALUES (expr1, expr2)
   WHEN condition2 THEN
      INTO table2 (col1, col2) VALUES (expr1, expr2)
   ELSE
      INTO table3 (col1, col2) VALUES (expr1, expr2)
SELECT ...
FROM source_table;

Example 1: Conditional Insert Based on Salary

Suppose we want to classify employees into different tables based on salary:

INSERT ALL
   WHEN salary < 3000 THEN
      INTO low_salary_emp (emp_id, name, salary)
      VALUES (emp_id, name, salary)
   WHEN salary BETWEEN 3000 AND 7000 THEN
      INTO mid_salary_emp (emp_id, name, salary)
      VALUES (emp_id, name, salary)
   ELSE
      INTO high_salary_emp (emp_id, name, salary)
      VALUES (emp_id, name, salary)
SELECT emp_id, name, salary
FROM employees;

What happens here?

• Each row from employees is evaluated
• Depending on salary:
  • inserted into one of three tables: *_salary
• All done in one SQL statement!

Example 2: Insert Into Multiple Tables (Same Row)

Unlike traditional INSERT, Oracle allows inserting the same row into multiple tables:

INSERT ALL
   WHEN department_id = 10 THEN
      INTO dept10_log (emp_id, name)
      VALUES (emp_id, name)
   WHEN salary > 5000 THEN
      INTO high_salary_log (emp_id, salary)
      VALUES (emp_id, salary)
SELECT emp_id, name, salary, department_id
FROM employees;

A single row can satisfy multiple conditions, and therefore be inserted into multiple targets.

INSERT ALL vs INSERT FIRST

Oracle also provides another variation:

INSERT FIRST

INSERT FIRST
   WHEN condition1 THEN
      INTO table1 ...
   WHEN condition2 THEN
      INTO table2 ...
   ELSE
      INTO table3 ...
SELECT ...
FROM source_table;

Key difference:

Use:

• INSERT ALL → when multiple inserts per row are allowed
• INSERT FIRST → when only the first matching condition should apply

Example 3: Using Constants and Expressions

You are not limited to source columns — expressions can be used:

INSERT ALL
   WHEN salary > 5000 THEN
      INTO bonus_table (emp_id, bonus)
      VALUES (emp_id, salary * 0.10)
SELECT emp_id, salary
FROM employees;

Performance Benefits

Using INSERT ALL ... WHEN provides:

• Single table scan of source data
• Reduced context switching between SQL statements
• Improved ETL performance

Instead of writing multiple INSERT statements, Oracle processes everything in one pass.

Common Use Cases

• Data warehousing (ETL pipelines)
• Data classification (e.g., risk levels, salary bands)
• Logging and auditing
• Data migration between schemas

Limitations and other Considerations

• Cannot use RETURNING INTO with multi-table inserts
• Debugging can be harder due to compact logic
• Conditions must be mutually understood (especially with INSERT FIRST)

Best Practices

• Keep conditions clear and non-overlapping when needed
• Use aliases in the SELECT clause for readability
• Test with small datasets before large batch inserts
• Prefer INSERT FIRST when logic should be exclusive

Conclusion

The INSERT ALL ... WHEN feature in Oracle Database is a powerful yet often underused tool.

It allows you to:

• Perform conditional logic directly in SQL
• Insert into multiple tables efficiently
• Simplify complex data transformation workflows

Mastering this feature can significantly improve your SQL expressiveness and performance, especially in data-intensive applications.

As a topic for a next article: I will try recounting real-life example of rewriting a multi-step ETL process using INSERT ALL —

I will state how we reduced both code complexity and execution time!

Please do not hesitate to let me know your questions or suggestions, thanks!
homepage@blog.parvu.org