Best way to do multi-row insert in Oracle?

Advanced inserting strategies

Local PL/SQL procedures

Content-based PL/SQL procedures can increase flexibility and clarity:

BEGIN
  FOR i IN 1..1000 LOOP
    INSERT INTO my_table (col1, col2) VALUES (i, 'value'||i);  -- Feeding the database, one at a time
  END LOOP;  -- End, but not the end of the world
END;


Or FORALL for speedy inserts:

DECLARE
  TYPE t_my_table IS TABLE OF my_table%ROWTYPE;  -- Custody battle for rowtypes
  v_my_data t_my_table := t_my_table();  -- Presto! Data generation
BEGIN
  -- Get creative with populating v_my_data here
  FORALL i IN v_my_data.FIRST .. v_my_data.LAST  -- Let the inserts commence!
    INSERT INTO my_table VALUES v_my_data(i);  -- Your table is chomping data now
END;


The SQL*Loader tactic

SQL*Loader is a boon for external data insertions:

sqlldr userid=myuser/mypassword control=mycontrol.ctl log=mylog.log  -- 'Cause log files are cool!


Craft your control file (mycontrol.ctl) with precision:

LOAD DATA
INFILE 'mydata.csv'  -- That's where the gold is!
INTO TABLE my_table
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'  -- Look, CSV doesn't bite!
(col1, col2)


Post loading, verify the data thrust into your table.

Visualization

A picture's worth a thousand words, so here's your database operation in emoji form:

🛤️ Database operation = The Oracle table

Inserting single rows 🚂🚃🚶‍♂️ -> 🚂🚃🧍‍♂️ is like one by one journey.

Whereas, multi-row inserts 🚂🚃👨‍👩‍👧‍👦 -> 🚂🚃👨‍👩‍👧‍👦 are an express service

And with speed the winner is 🚂💺1️⃣ -> 🐢😪 VERSUS 🚂💺👨‍👩‍👧‍👦 -> 🚀💨💨💨🔥

📊 Tl;dr: Multi-row boosts speed and optimizes database interaction.

Optimizing inserts further

Data transfer efficiency

An efficient path for transferring data between tables is INSERT INTO ... SELECT:

INSERT INTO dest_table (col1, col2)
SELECT col1, col2 FROM source_table;  -- Teleportation works, in databases!


Partitioning for large data sets

For large-scale operations, partition your inserts for better resource management and to dodge single-statement inserts constraints.

Concurrency optimization

In concurrent scenarios, use hints or APPEND to give your inserts a speed boost.