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');

I have found a good article related to this https://database.guide/4-ways-to-insert-multiple-rows-in-oracle/ — vikas etagi, May 20 '22 at 07:41

score 488 · Answer 1 · edited May 27 '23 at 04:03

488

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.

edited May 27 '23 at 04:03

Jon Heller

34,999
6
74
132

answered Sep 18 '08 at 15:43

Myto

4,995
2
15
4

90

I don't understand what `SELECT 1 FROM DUAL` does. – jameshfisher Mar 28 '13 at 12:23
4

According to [this tutorial page](http://www.techonthenet.com/sql/insert.php), `SELECT * FROM DUAL` works too. – Rory O'Kane Jun 05 '13 at 16:49
1

@jameshfisher I don't know why multiple-insert requires the selection from `DUAL`, but I can tell you what `DUAL` is: [a pre-defined table](http://en.wikipedia.org/wiki/DUAL_table). Run `SELECT * FROM DUAL` to view it. – Rory O'Kane Jun 05 '13 at 16:51
84

`INSERT ALL` requires a `SELECT` subquery. To get around that, `SELECT 1 FROM DUAL` is used to give a single row of dummy data. – Markus Jarderot Jun 25 '13 at 08:17
62

How does this differ from multiple insert statements? You still have the repetition on the column names so don't seem to gain much. – Burhan Ali Mar 21 '14 at 12:32
48

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. – Kent Pawar Apr 22 '14 at 21:30
24

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. – user1412523 Dec 03 '15 at 12:03
6

@user1412523 - 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. – Bob Jarvis - Слава Україні Aug 25 '16 at 17:15
1

Would this be an optimal/optimized way to insert 40k records at once ? – user1220169 Sep 29 '16 at 02:52
1

Compared tot he version Espo posted, this is significantly slower. this approach costed me about 2 minutes for 900 lines, while his costs seconds. – DaMachk Oct 11 '16 at 13:22
1

See here for a detailed explanation about the NEXTVAL limitations https://stackoverflow.com/questions/28523262/multiple-insert-sql-oracle – Philippe Nov 09 '16 at 06:13
11

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. – Rade_303 Jun 14 '17 at 09:03
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 @Myto correctly showed in Oracle there are no commas between the 'into' clauses. – drewdqueue May 08 '20 at 07:55
I solve for me. For different examples: https://database.guide/4-ways-to-insert-multiple-rows-in-oracle/ – serkanayaz Nov 21 '22 at 13:16
@user1220169 If you do this via jdbc it will throw `ORA-00913: too many values` for large records. Use stackoverflow.com/a/3786127/1097600 instead – Sorter Dec 27 '22 at 10:15
@jameshfisher - 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. – Mark Roworth Feb 10 '23 at 15:11

score 220 · Accepted Answer · edited Dec 29 '20 at 14:34

220

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.

edited Dec 29 '20 at 14:34

Saša

4,416
1
27
41

answered Sep 02 '08 at 14:08

Espo

41,399
21
132
159

9

There is also something called "Insert All" as of 9i(?) – mlathe Nov 11 '10 at 19:30
5

Being picky, but the formatting makes more sense if you put "union all" at the end of each select line (except for the last). – Jamie Apr 25 '17 at 20:34
5

One disadvantage with this is we cant use a `sequnce.nextval` as it is prohibited in `union`of `select`. Instead we can go with `INSERT ALL`. – sql_dummy May 08 '17 at 02:41
6

@Jamie : 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. – Laurent.B May 24 '17 at 08:19
How does this answer the question exactly? What's `from dual` and where is the actual data being inserted? – Tomáš Zato Nov 24 '18 at 19:23
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.* – Rafael Andrade Jan 10 '19 at 17:56
I wonder which method is faster—using`select` 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. – Anton Shepelev May 27 '20 at 16:47
If I want the auto-generated IDs back as result...what do I do? – deostroll Mar 08 '22 at 05:09
@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 ALL`s 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. – Manngo Apr 09 '23 at 01:04
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. – Manngo Apr 09 '23 at 01:11

score 35 · Answer 3 · edited Nov 17 '10 at 10:24

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

The ID column in my table is autogenerated. Can I simply skip the ID field in the loader control file? — Thom DeCarlo, Sep 08 '17 at 14:18
@Thom, use the sequence.nextval e.g. `fruit_id "fruit_seq.nextval"` in the column definition — roblogic, Feb 08 '18 at 06:07

score 28 · Answer 4 · answered Sep 18 '08 at 10:27

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;
/

This answer works great, especially if you need to use sequences. Upvoted. — diekunstderfuge, Jun 24 '21 at 20:17

score 15 · Answer 5 · edited May 23 '17 at 12:26

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.

score 8 · Answer 6 · answered Nov 01 '18 at 17:19

8

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;

answered Nov 01 '18 at 17:19

Girdhar Singh Rathore

5,030
7
49
67

score -1 · Answer 7 · answered Jun 12 '18 at 09:05

-1

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

answered Jun 12 '18 at 09:05

Vasanth Raghavan

162
2
12

score -1 · Answer 8 · answered Aug 09 '19 at 12:36

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

score -1 · Answer 9 · answered Nov 01 '19 at 17:06

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

Best way to do multi-row insert in Oracle?

9 Answers9

Linked

Related