The INSERT ALL command in Oracle PL/SQL is a powerful feature for inserting multiple data rows into one or more tables efficiently.
This will simplify data insertion logic, reduces data redundancy, and improves performance when dealing with bulk data operations.
What is the INSERT ALL Command?
The INSERT ALL statement allows multiple INSERT operations to be performed in a single command, which improves efficiency and minimizes network round-trips.
Syntax of INSERT ALL
INSERT ALL INTO table1 (column1, column2) VALUES (value1, value2) INTO table2 (column1, column2) VALUES (value3, value4)SELECT * FROM dual;
Examples of Using INSERT ALL
1. Inserting Data into Multiple Tables
INSERT ALL INTO employees_dept (id, name, department) VALUES (1, 'ABC_123', 'Sales') INTO employees_old (id, name, department) VALUES (2, 'XYZ_567', 'Sales')SELECT * FROM dual;
2. Conditional Insertion with WHEN
INSERT ALL also allows conditional logic for row insertion.
INSERT ALL WHEN salary > 10000 THEN INTO high_employee_earners (id, name, salary) VALUES (id, name, salary) WHEN salary <= 5000 THEN INTO normal_earners (id, name, salary) VALUES (id, name, salary)
SELECT id, name, salary FROM employees;
This syntax can be used and modified accordingly for your requirements.
