By Jamey


2008-09-02 13:56:19 8 Comments

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

8 comments

@akasha 2019-08-09 12:36:43

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

@Girdhar Singh Rathore 2018-11-01 17:19:19

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;

@Vasanth Raghavan 2018-06-12 09:05:28

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

@Matthew Watson 2008-09-03 02:32:46

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

@roblogic 2016-05-31 12:45:50

This should be the top answer IMHO, anything else (for large scale tasks) is asking for trouble

@Thom DeCarlo 2017-09-08 14:18:28

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

@roblogic 2018-02-08 06:07:47

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

@Toolkit 2018-05-29 17:14:43

50 million records in few minutes. Way to go

@Espo 2008-09-02 14:08:12

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.

(source)

@mlathe 2010-11-11 19:30:58

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

@Jamie 2017-04-25 20:34:04

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

@sql_dummy 2017-05-08 02:41:53

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.

@Laurent.B 2017-05-24 08:19:57

@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.

@Toolkit 2018-05-29 16:27:46

what is the maximum for 12c?

@Tomáลก Zato 2018-11-24 19:23:53

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

@Rafael Andrade 2019-01-10 17:56:59

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.

@Myto 2008-09-18 15:43:50

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;

@jameshfisher 2013-03-28 12:23:27

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

@Rory O'Kane 2013-06-05 16:49:53

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

@Rory O'Kane 2013-06-05 16:51:19

@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. Run SELECT * FROM DUAL to view it.

@Markus Jarderot 2013-06-25 08:17:17

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

@Burhan Ali 2014-03-21 12:32:21

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

@Kent Pawar 2014-04-22 21:30:35

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.

@user1412523 2015-12-03 12:03:25

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.

@Bob Jarvis 2016-08-25 17:15:51

@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.

@user1220169 2016-09-29 02:52:48

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

@DaMachk 2016-10-11 13:22:45

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

@Philippe 2016-11-09 06:13:47

See here for a detailed explanation about the NEXTVAL limitations stackoverflow.com/questions/28523262/multiple-insert-sql-ora‌​cle

@RockyMM 2017-06-14 09:03:33

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.

@Tony Andrews 2008-09-18 10:27:38

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

@Ryan Ahearn 2008-09-02 14:08:58

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.

Related Questions

Sponsored Content

28 Answered Questions

[SOLVED] Fastest Way of Inserting in Entity Framework

7 Answered Questions

29 Answered Questions

[SOLVED] How to select the nth row in a SQL database table?

11 Answered Questions

[SOLVED] Can I concatenate multiple MySQL rows into one field?

23 Answered Questions

[SOLVED] Insert into ... values ( SELECT ... FROM ... )

25 Answered Questions

[SOLVED] MyISAM versus InnoDB

13 Answered Questions

[SOLVED] Best way to get identity of inserted row?

  • 2008-09-03 21:32:02
  • Oded
  • 767856 View
  • 1023 Score
  • 13 Answer
  • Tags:   sql sql-server tsql

15 Answered Questions

[SOLVED] Oracle: If Table Exists

  • 2009-11-25 18:46:10
  • Alan Storm
  • 433740 View
  • 308 Score
  • 15 Answer
  • Tags:   sql oracle sql-drop

12 Answered Questions

[SOLVED] Oracle: how to UPSERT (update or insert into a table?)

  • 2008-10-26 01:24:06
  • Mark Harrison
  • 383161 View
  • 269 Score
  • 12 Answer
  • Tags:   sql oracle merge upsert

24 Answered Questions

[SOLVED] Is it possible to insert multiple rows at a time in an SQLite database?

  • 2009-10-22 20:04:42
  • Andrew
  • 352832 View
  • 524 Score
  • 24 Answer
  • Tags:   sql sqlite syntax

Sponsored Content