Best way to do multi-row insert in Oracle?

Question

I'm looking for a good way to perform multi-row inserts into an Oracle 9 database. The following works in MySQL but doesn't seem to be supported in Oracle.

INSERT INTO TMP_DIM_EXCH_RT 
(EXCH_WH_KEY, 
 EXCH_NAT_KEY, 
 EXCH_DATE, EXCH_RATE, 
 FROM_CURCY_CD, 
 TO_CURCY_CD, 
 EXCH_EFF_DATE, 
 EXCH_EFF_END_DATE, 
 EXCH_LAST_UPDATED_DATE) 
VALUES
    (1, 1, '28-AUG-2008', 109.49, 'USD', 'JPY', '28-AUG-2008', '28-AUG-2008', '28-AUG-2008'),
    (2, 1, '28-AUG-2008', .54, 'USD', 'GBP', '28-AUG-2008', '28-AUG-2008', '28-AUG-2008'),
    (3, 1, '28-AUG-2008', 1.05, 'USD', 'CAD', '28-AUG-2008', '28-AUG-2008', '28-AUG-2008'),
    (4, 1, '28-AUG-2008', .68, 'USD', 'EUR', '28-AUG-2008', '28-AUG-2008', '28-AUG-2008'),
    (5, 1, '28-AUG-2008', 1.16, 'USD', 'AUD', '28-AUG-2008', '28-AUG-2008', '28-AUG-2008'),
    (6, 1, '28-AUG-2008', 7.81, 'USD', 'HKD', '28-AUG-2008', '28-AUG-2008', '28-AUG-2008');

Olvan

I have found a good article related to this database.guide/4-ways-to-insert-multiple-rows-in-oracle – Celenacelene

Answer 1

This works in Oracle:

insert into pager (PAG_ID,PAG_PARENT,PAG_NAME,PAG_ACTIVE)
          select 8000,0,'Multi 8000',1 from dual
union all select 8001,0,'Multi 8001',1 from dual

The thing to remember here is to use the from dual statement.

Camelopardalis

There is also something called "Insert All" as of 9i(?) – Phylloquinone

Being picky, but the formatting makes more sense if you put "union all" at the end of each select line (except for the last). – Simple

One disadvantage with this is we cant use a sequnce.nextval as it is prohibited in unionof select. Instead we can go with INSERT ALL. – Ifni

@Simple : the formatting of Espo is slightly smarter in the sense that you don't have to worry about whether you are on the last line or not, when adding new lines. Hence, once you have your 2 first selects, you can easily copy/paste the last line (or a middle one), only focusing on the values that you have to change. It's a common trick for plenty of other cases in any languages (comma, logic operators, plus...). It's just a matter of habit, lots of former practices have been revised to focus on the liability of the code more than intuitiveness. – Weirdo

How does this answer the question exactly? What's from dual and where is the actual data being inserted? – Toddtoddie

What if I need to insert next value from sequence (sequence.nextval) ? I am getting this error 02287. 00000 - "sequence number not allowed here" *Cause: The specified sequence number (CURRVAL or NEXTVAL) is inappropriate here in the statement. – Meliorism

I wonder which method is faster—usingselect and union or INSERT ALL? Both seems redundant for the frequent task of inserting many rows into the same table. MySql has made a good decision in providing a specified syntax for it. – Diaphony

If I want the auto-generated IDs back as result...what do I do? – Gastrotomy

@TomášZato Short answer is that Oracle doesn’t do a simple multi-row insert. However it will copy from a table or a virtual table using INSERT INTO. The collection of UNION ALLs create a combination of rows. You use UNION ALL rather than UNION because (a) it’s faster and (b) to accept duplicates. The FROM DUAL is because Oracle won’t let you generate literals without a FROM clause, so they provide a dummy DUAL table for the purpose. – Musselman

This is the technique I use, and I have found that it’s infinitely faster than multiple individual INSERT statements. However, I have also found that the larger the data set, the much longer the time. I had about 15000 rows to insert, and found that the process crashed after about 5 minutes because it blew the memory. By breaking it up into 15 smaller data sets it did the job in a fraction of the time. To put it another way, it doesn’t seem to scale vary well. – Musselman

Yes, this is way faster than INSERT ALL. In my case, I batched 990 rows. – Maudemaudie

Answer 2

In Oracle, to insert multiple rows into table t with columns col1, col2 and col3 you can use the following syntax:

INSERT ALL
   INTO t (col1, col2, col3) VALUES ('val1_1', 'val1_2', 'val1_3')
   INTO t (col1, col2, col3) VALUES ('val2_1', 'val2_2', 'val2_3')
   INTO t (col1, col2, col3) VALUES ('val3_1', 'val3_2', 'val3_3')
   .
   .
   .
SELECT 1 FROM DUAL;

---

In Oracle 23c, you can insert multiple rows with this simplified syntax:

INSERT INTO t(col1, col2, col3) VALUES
('val1_1', 'val1_2', 'val1_3'),
('val2_1', 'val2_2', 'val2_3'),
('val3_1', 'val3_2', 'val3_3');

For inserting a large number of rows, the new syntax is much faster than the older INSERT ALL approach and about as fast as the UNION ALL approach. However, due to exponentially increasing parse times, you still want to avoid inserting more than about 1000 rows at a time.

Medley

I don't understand what SELECT 1 FROM DUAL does. – Lit

According to this tutorial page, SELECT * FROM DUAL works too. – Gromyko

@Lit I don't know why multiple-insert requires the selection from DUAL, but I can tell you what DUAL is: a pre-defined table. Run SELECT * FROM DUAL to view it. – Gromyko

INSERT ALL requires a SELECT subquery. To get around that, SELECT 1 FROM DUAL is used to give a single row of dummy data. – Darees

How does this differ from multiple insert statements? You still have the repetition on the column names so don't seem to gain much. – Gynecoid

Around 10-12 Multiple INSERT statements get completed in 2secs on my PC, while the above syntax is able to INSERT 1000 records per sec! Impressed! Note that I COMMIT only at the end. – Zilpah

This works fine, however if you are inserting using a sequence , say user.NEXTVAL it will return the same value for each insert. You could manually increment it in the insert all, then update the sequence outside of the insert. – Sustentation

@Sustentation - which is why you should have an ON INSERT...FOR EACH ROW trigger on the table to get values from your sequence and assign them to the primary key column on each row which is inserted. Plus, it means the application code doesn't have to know which sequence to use, etc. – Pt

Would this be an optimal/optimized way to insert 40k records at once ? – Sophia

Compared tot he version Espo posted, this is significantly slower. this approach costed me about 2 minutes for 900 lines, while his costs seconds. – Synge

See here for a detailed explanation about the NEXTVAL limitations https://mcmap.net/q/47006/-multiple-insert-sql-oracle – Aprilaprile

Just wanted to share that for these kinds of idiosyncrasies and non-conforming to standards (SQL-92) I find really puzzling why Oracle still gets chosen as the database of choice. – Poetry

Excellent answer and just made my life so much easier (translating hundreds of excel rows into an insert statement). Just remember that although it looks wrong per MySQL and original question, as @Medley correctly showed in Oracle there are no commas between the 'into' clauses. – Duckling

I solve for me. For different examples: database.guide/4-ways-to-insert-multiple-rows-in-oracle – Perilymph

@Sophia If you do this via jdbc it will throw ORA-00913: too many values for large records. Use https://mcmap.net/q/47007/-efficient-way-to-do-batch-inserts-with-jdbc instead – Satyr

@Lit - dual is a badly named imaginary table, which, despite being named dual, contains one virtual row, I believe with no columns. dummy would be a better name for it. – Inadvisable

Answer 3

Use SQL*Loader. It takes a little setting up, but if this isn't a one off, its worth it.

Create Table

SQL> create table ldr_test (id number(10) primary key, description varchar2(20));
Table created.
SQL>

Create CSV

oracle-2% cat ldr_test.csv
1,Apple
2,Orange
3,Pear
oracle-2% 

Create Loader Control File

oracle-2% cat ldr_test.ctl 
load data

 infile 'ldr_test.csv'
 into table ldr_test
 fields terminated by "," optionally enclosed by '"'              
 ( id, description )

oracle-2% 

Run SQL*Loader command

oracle-2% sqlldr <username> control=ldr_test.ctl
Password:

SQL*Loader: Release 9.2.0.5.0 - Production on Wed Sep 3 12:26:46 2008

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Commit point reached - logical record count 3

Confirm insert

SQL> select * from ldr_test;

        ID DESCRIPTION
---------- --------------------
         1 Apple
         2 Orange
         3 Pear

SQL>

SQL*Loader has alot of options, and can take pretty much any text file as its input. You can even inline the data in your control file if you want.

Here is a page with some more details -> SQL*Loader

Baldhead

The ID column in my table is autogenerated. Can I simply skip the ID field in the loader control file? – Succumb

@Thom, use the sequence.nextval e.g. fruit_id "fruit_seq.nextval" in the column definition – Grizel

50 million records in few minutes. Way to go – Crossways

Answer 4

Whenever I need to do this I build a simple PL/SQL block with a local procedure like this:

declare
   procedure ins
   is
      (p_exch_wh_key INTEGER, 
       p_exch_nat_key INTEGER, 
       p_exch_date DATE, exch_rate NUMBER, 
       p_from_curcy_cd VARCHAR2, 
       p_to_curcy_cd VARCHAR2, 
       p_exch_eff_date DATE, 
       p_exch_eff_end_date DATE, 
       p_exch_last_updated_date DATE);
   begin
      insert into tmp_dim_exch_rt 
      (exch_wh_key, 
       exch_nat_key, 
       exch_date, exch_rate, 
       from_curcy_cd, 
       to_curcy_cd, 
       exch_eff_date, 
       exch_eff_end_date, 
       exch_last_updated_date) 
      values
      (p_exch_wh_key, 
       p_exch_nat_key, 
       p_exch_date, exch_rate, 
       p_from_curcy_cd, 
       p_to_curcy_cd, 
       p_exch_eff_date, 
       p_exch_eff_end_date, 
       p_exch_last_updated_date);
   end;
begin
   ins (1, 1, '28-AUG-2008', 109.49, 'USD', 'JPY', '28-AUG-2008', '28-AUG-2008', '28-AUG-2008'),
   ins (2, 1, '28-AUG-2008', .54, 'USD', 'GBP', '28-AUG-2008', '28-AUG-2008', '28-AUG-2008'),
   ins (3, 1, '28-AUG-2008', 1.05, 'USD', 'CAD', '28-AUG-2008', '28-AUG-2008', '28-AUG-2008'),
   ins (4, 1, '28-AUG-2008', .68, 'USD', 'EUR', '28-AUG-2008', '28-AUG-2008', '28-AUG-2008'),
   ins (5, 1, '28-AUG-2008', 1.16, 'USD', 'AUD', '28-AUG-2008', '28-AUG-2008', '28-AUG-2008'),
   ins (6, 1, '28-AUG-2008', 7.81, 'USD', 'HKD', '28-AUG-2008', '28-AUG-2008', '28-AUG-2008');
end;
/

Persecution

This answer works great, especially if you need to use sequences. Upvoted. – Electrograph

Answer 5

If you have the values that you want to insert in another table already, then you can Insert from a select statement.

INSERT INTO a_table (column_a, column_b) SELECT column_a, column_b FROM b_table;

Otherwise, you can list a bunch of single row insert statements and submit several queries in bulk to save the time for something that works in both Oracle and MySQL.

@Espo's solution is also a good one that will work in both Oracle and MySQL if your data isn't already in a table.

Liaoning

Answer 6

you can insert using loop if you want to insert some random values.

BEGIN 
    FOR x IN 1 .. 1000 LOOP
         INSERT INTO MULTI_INSERT_DEMO (ID, NAME)
         SELECT x, 'anyName' FROM dual;
    END LOOP;
END;

Arawakan

Answer 7

Cursors may also be used, although it is inefficient. The following stackoverflow post discusses the usage of cursors :

INSERT and UPDATE a record using cursors in oracle

Erythema

Answer 8

Here is a very useful step by step guideline for insert multi rows in Oracle:

https://livesql.oracle.com/apex/livesql/file/content_BM1LJQ87M5CNIOKPOWPV6ZGR3.html

The last step:

INSERT ALL
/* Everyone is a person, so insert all rows into people */
WHEN 1=1 THEN
INTO people (person_id, given_name, family_name, title)
VALUES (id, given_name, family_name, title)
/* Only people with an admission date are patients */
WHEN admission_date IS NOT NULL THEN
INTO patients (patient_id, last_admission_date)
VALUES (id, admission_date)
/* Only people with a hired date are staff */
WHEN hired_date IS NOT NULL THEN
INTO staff (staff_id, hired_date)
VALUES (id, hired_date)
  WITH names AS (
    SELECT 4 id, 'Ruth' given_name, 'Fox' family_name, 'Mrs' title,
           NULL hired_date, DATE'2009-12-31' admission_date
    FROM   dual UNION ALL
    SELECT 5 id, 'Isabelle' given_name, 'Squirrel' family_name, 'Miss' title ,
           NULL hired_date, DATE'2014-01-01' admission_date
    FROM   dual UNION ALL
    SELECT 6 id, 'Justin' given_name, 'Frog' family_name, 'Master' title,
           NULL hired_date, DATE'2015-04-22' admission_date
    FROM   dual UNION ALL
    SELECT 7 id, 'Lisa' given_name, 'Owl' family_name, 'Dr' title,
           DATE'2015-01-01' hired_date, NULL admission_date
    FROM   dual
  )
  SELECT * FROM names

Reiser

Answer 9

In my case, I was able to use a simple insert statement to bulk insert many rows into TABLE_A using just one column from TABLE_B and getting the other data elsewhere (sequence and a hardcoded value) :

INSERT INTO table_a (
    id,
    column_a,
    column_b
)
    SELECT
        table_a_seq.NEXTVAL,
        b.name,
        123
    FROM
        table_b b;

Result:

ID: NAME: CODE:
1, JOHN, 123
2, SAM, 123
3, JESS, 123

etc

Crandale