SQL 在 Oracle 中进行多行插入的最佳方法？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/39576/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Best way to do multi-row insert in Oracle?
提问by Jamey
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.
我正在寻找一种在 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');
采纳答案by Espo
回答by Myto
In Oracle, to insert multiple rows into table t with columns col1, col2 and col3 you can use the following syntax:
在 Oracle 中，要将多行插入到包含 col1、col2 和 col3 列的表 t 中，您可以使用以下语法：
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;
回答by Matthew Watson
Use SQL*Loader. It takes a little setting up, but if this isn't a one off, its worth it.
使用 SQL*Loader。这需要一些设置，但如果这不是一次性的，那就值得了。
Create Table
创建表
SQL> create table ldr_test (id number(10) primary key, description varchar2(20));
Table created.
SQL>
Create CSV
创建 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
运行 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
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.
SQL*Loader 有很多选项，可以将几乎任何文本文件作为其输入。如果需要，您甚至可以内联控制文件中的数据。
Here is a page with some more details -> SQL*Loader
这是一个包含更多详细信息的页面 -> SQL*Loader
回答by Matthew Watson
Whenever I need to do this I build a simple PL/SQL block with a local procedure like this:
每当我需要这样做时，我都会使用这样的本地过程构建一个简单的 PL/SQL 块：
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;
/
回答by Ryan Ahearn
If you have the values that you want to insert in another table already, then you can Insert from a select statement.
如果您已经有要插入另一个表中的值，则可以从 select 语句中插入。
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.
否则，您可以列出一堆单行插入语句并批量提交多个查询，以节省时间，以便在 Oracle 和 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.
@Espo的解决方案也是一个很好的解决方案，如果您的数据不在表中，它可以在 Oracle 和 MySQL 中使用。
回答by Girdhar Singh Rathore
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;
回答by akasha
Here is a very useful step by step guideline for insert multi rows in Oracle:
这是在 Oracle 中插入多行的非常有用的分步指南：
https://livesql.oracle.com/apex/livesql/file/content_BM1LJQ87M5CNIOKPOWPV6ZGR3.html
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
回答by java-addict301
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) :
就我而言，我能够使用一个简单的插入语句将多行批量插入到 TABLE_A 中，仅使用 TABLE_B 中的一列并在其他地方获取其他数据（序列和硬编码值）：
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
等等
回答by Vasanth Raghavan
Cursors may also be used, although it is inefficient. The following stackoverflow post discusses the usage of cursors :
也可以使用游标，尽管它效率低下。以下 stackoverflow 帖子讨论了游标的用法：
