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

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

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

Solution 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

Solution 4

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.

Solution 5

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;

View more solutions