Skip to content

Oracle INSERT ALL (Multi-Table) 📦📦

Mentor's Note: Imagine you have one crate of fruits, and you need to put Apples in one basket and Oranges in another. INSERT ALL lets you take data from one source and distribute it into many different tables at once! 💡

🌟 The Scenario: The Customer Onboarding 📂

When a new customer joins: 1. You want to save their name in the CONTACTS table. 2. You want to save their initial balance in the ACCOUNTS table. - Goal: Do both in one single command.

💻 1. Unconditional INSERT ALL

Inserts every row from the source into all target tables.

INSERT ALL
  INTO contacts (id, name) VALUES (user_id, user_name)
  INTO accounts (id, balance) VALUES (user_id, 0)
SELECT user_id, user_name FROM signup_form;

💻 2. Conditional INSERT ALL (Filtering) 🚦

Inserts into specific tables based on a condition.

INSERT ALL
  WHEN salary > 50000 THEN
    INTO high_earners (id, sal) VALUES (id, salary)
  WHEN salary <= 50000 THEN
    INTO standard_earners (id, sal) VALUES (id, salary)
SELECT id, salary FROM employees;

🏗️ Architect's Note: Performance 🛡️

INSERT ALL is more efficient than running multiple separate INSERT statements. - The Architect's Secret: Oracle performs one single scan of the source data. This reduces "Buffer Gets" and "CPU cycles." - Tip: This is extremely useful for ETL processes (Extract, Transform, Load) when moving data from staging to production tables.

📈 Learning Path