我正在寻找一种很好的方法来执行多行插入到Oracle 9数据库。以下内容在MySQL中有效,但在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');
下面是一个非常有用的在Oracle中插入多行的指南:
https://livesql.oracle.com/apex/livesql/file/content_BM1LJQ87M5CNIOKPOWPV6ZGR3.html
最后一步:
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
使用SQL *加载程序。这需要一点设置,但如果这不是唯一的,它是值得的。
创建表
SQL> create table ldr_test (id number(10) primary key, description varchar2(20));
Table created.
SQL>
创建CSV
oracle-2% cat ldr_test.csv
1,Apple
2,Orange
3,Pear
oracle-2%
创建Loader控制文件
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%
执行SQL*Loader命令
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
确认插入
SQL> select * from ldr_test;
ID DESCRIPTION
---------- --------------------
1 Apple
2 Orange
3 Pear
SQL>
SQL*Loader有很多选项,并且可以将几乎任何文本文件作为输入。如果需要,您甚至可以将数据内联到控制文件中。
这里有一个页面,有更多的细节-> SQL*Loader