Best way to do multi-row insert in Oracle?
SqlOracleSql InsertOracle9iSql Problem Overview
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');
Sql Solutions
Solution 1 - Sql
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 2 - Sql
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 3 - Sql
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 - Sql
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;
/
Solution 5 - Sql
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 6 - Sql
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;
Solution 7 - Sql
I wonder if a simple batch statement in an anonymous block would be an option:
create table foo(id int);
begin
insert into foo(id) values (1);
insert into foo(id) values (2);
end;
/
Source:
db<>fiddle/Top Answers feature request - Oracle support on dbfiddle doesn't allow multiple statements in one batch
Solution 8 - Sql
insert all
into table_name (columns list) values(values list)
into table_name (columns list) values(values list)
.......
.......
into table_name (columns list) values(values list)
select * from dual;
Solution 9 - Sql
Cursors may also be used, although it is inefficient. The following stackoverflow post discusses the usage of cursors :
https://stackoverflow.com/questions/11921889/insert-and-update-a-record-using-cursors-in-oracle
Solution 10 - Sql
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
Solution 11 - Sql
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