Skip to content

Multiple Inserts & Bulk Loading 📦

Mentor's Note: In the real world, you rarely insert one row at a time. Whether you are migrating data or importing a CSV, you need to handle batches. Efficiency is key here! 💡

🌟 The Scenario: The Wholesale Order 🚛

Imagine you are a supplier delivering water bottles.

  • Single Insert: Carrying one bottle at a time to the fridge. (Exhausting! 🥵)
  • Multiple Insert: Using a crate to carry 24 bottles at once. (Efficient! ⚡)
  • INSERT INTO SELECT: Transferring all bottles from a truck directly into the warehouse.

💻 1. Inserting Multiple Rows (One Query)

Most modern databases allow you to insert many rows in a single statement.

INSERT INTO students (roll_no, name)
VALUES 
    (105, 'Sneha'),
    (106, 'Vikram'),
    (107, 'Amit');

-- Oracle uses 'INSERT ALL' for multiple rows
INSERT ALL
  INTO students (roll_no, name) VALUES (105, 'Sneha')
  INTO students (roll_no, name) VALUES (106, 'Vikram')
SELECT * FROM dual;

💻 2. Copying Data (INSERT INTO SELECT) 📸

This is the most powerful way to move data between tables. It "SELECTS" from one table and "INSERTS" into another.

-- Scenario: Moving 'Active' students to a 'Graduates' archive
INSERT INTO graduates (id, name, grade)
SELECT id, name, grade
FROM students
WHERE status = 'Completed';

💻 3. Bulk Loading from External Files

If you have a .csv file with 1 million rows, don't use INSERT. Use the database's specific bulk loader.

  • MySQL: LOAD DATA INFILE 'file.csv' ...
  • PostgreSQL: \copy table FROM 'file.csv' ...
  • Oracle: SQL*Loader or External Tables.

💡 Performance Tip

"Inserting 1,000 rows in one query is significantly faster than running 1,000 separate INSERT statements. Each separate statement requires a 'round-trip' to the server, which adds overhead!"

📈 Learning Path