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

Myto.. 335

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

`INSERT ALL`需要一个`SELECT`子查询.为了解决这个问题,使用`SELECT 1 FROM DUAL`来提供单行虚拟数据. (53认同)

我不明白`SELECT 1 FROM DUAL'的作用. (49认同)

这与多个插入语句有何不同？你仍然对列名重复,所以似乎没有多少收获. (34认同)

大约10-12个多个INSERT语句在我的电脑上以2秒完成,而上述语法能够每秒插入1000个记录!感动!请注意,我只在最后进行COMMIT. (26认同)

这工作正常,但是如果您使用序列插入,比如user.NEXTVAL,它将为每个插入返回相同的值.您可以在insert all中手动递增它,然后更新插入之外的序列. (13认同)

只是想分享一下这些特性和不符合标准(SQL-92)我真的很困惑为什么Oracle仍然被选为首选数据库. (7认同)

@ user1412523 - 这就是为什么你应该在表上有一个`ON INSERT ... FOR EACH ROW`触发器来获取序列中的值并将它们分配给插入的每一行的主键列.另外,这意味着应用程序代码不必知道要使用哪个序列等. (4认同)

根据[本教程页面](http://www.techonthenet.com/sql/insert.php),`SELECT*FROM DUAL`也可以. (3认同)

---

Espo.. 155

这适用于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

这里要记住的是使用该from dual语句.

(来源)

1> Myto..：

---

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

---

`INSERT ALL`需要一个`SELECT`子查询.为了解决这个问题,使用`SELECT 1 FROM DUAL`来提供单行虚拟数据.

---

我不明白`SELECT 1 FROM DUAL'的作用.

---

这与多个插入语句有何不同？你仍然对列名重复,所以似乎没有多少收获.

---

大约10-12个多个INSERT语句在我的电脑上以2秒完成,而上述语法能够每秒插入1000个记录!感动!请注意,我只在最后进行COMMIT.

---

这工作正常,但是如果您使用序列插入,比如user.NEXTVAL,它将为每个插入返回相同的值.您可以在insert all中手动递增它,然后更新插入之外的序列.

---

只是想分享一下这些特性和不符合标准(SQL-92)我真的很困惑为什么Oracle仍然被选为首选数据库.

---

@ user1412523 - 这就是为什么你应该在表上有一个`ON INSERT ... FOR EACH ROW`触发器来获取序列中的值并将它们分配给插入的每一行的主键列.另外,这意味着应用程序代码不必知道要使用哪个序列等.

---

根据[本教程页面](http://www.techonthenet.com/sql/insert.php),`SELECT*FROM DUAL`也可以.

2> Espo..：

---

这适用于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

这里要记住的是使用该from dual语句.

(来源)

---

从9i开始,还有一个名为"Insert All"的东西(？)

---

挑剔,但如果你在每个选择行的末尾加上"union all"(除了最后一行),格式化就更有意义了.

---

@Jamie:Espo的格式稍微聪明,因为你不必担心在添加新行时是否在最后一行.因此,一旦你有2个第一次选择,你可以轻松地复制/粘贴最后一行(或中间一行),只关注你必须改变的值.对于任何语言中的大量其他案例(逗号,逻辑运算符,加......),这是一个常见的技巧.这只是一个习惯问题,许多以前的做法都经过修改,专注于代码的责任而不是直觉.

3> Matthew Wats..：

---

使用SQL*Loader.它需要一点设置,但如果这不是一次性的,它是值得的.

创建表

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% 

创建加载程序控制文件

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

4> 小智..：

---

每当我需要这样做时,我用这样的本地过程构建一个简单的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;
/

5> Ryan Ahearn..：

---

如果您已经在另一个表中插入了值,则可以从select语句中插入.

INSERT INTO a_table (column_a, column_b) SELECT column_a, column_b FROM b_table;

否则,您可以列出一堆单行插入语句并批量提交多个查询,以节省在Oracle和MySQL中工作的时间.

@Espo的解决方案也是一个很好的解决方案,如果您的数据不在表格中,它将在Oracle和MySQL中都有效.