logo标签列表

在Oracle中进行多行插入的最佳方法是什么？

sqloraclesql-insertoracle9i
374

我正在寻找一种在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');
我找到了一篇与此相关的好文章 https://database.guide/4-ways-to-insert-multiple-rows-in-oracle/ - vikas etagi
9个回答
495
在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;
在 Oracle 23c 中，您可以使用这个简化的语法插入多行：
INSERT INTO t(col1, col2, col3) VALUES
('val1_1', 'val1_2', 'val1_3'),
('val2_1', 'val2_2', 'val2_3'),
('val3_1', 'val3_2', 'val3_3');

对于插入大量行，新的语法比旧的INSERT ALL方法快得多，与UNION ALL方法的速度差不多。然而，由于指数级增长的解析时间，您仍然希望避免一次性插入超过1000行。

90
我不明白 SELECT 1 FROM DUAL 是什么意思。 - jameshfisher
84
“INSERT ALL”需要一个“SELECT”子查询。为了避免这种情况，可以使用“SELECT 1 FROM DUAL”来提供一行虚拟数据。 - Markus Jarderot
62
与多个插入语句相比，这有何不同？你仍然需要重复列名，所以好像没有得到很多提升。 - Burhan Ali
48
在我的电脑上，大约需要 2 秒钟才能完成 10-12 条多重插入语句，而上述语法可以每秒插入 1000 条记录！令人印象深刻！请注意，我只在最后进行提交。 - Kent Pawar
24
这个可以正常工作，但是如果你使用一个序列（sequence）来插入数据，比如说 user.NEXTVAL，每次插入的值都会相同。你可以在插入所有数据时手动递增它的值，然后在插入之外更新这个序列。 - user1412523
显示剩余11条评论
222

这在 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 语句。

9
从9i开始，还有一个叫做"插入全部"的东西。 - mlathe
5
有点挑剔，但如果在每个选择行的末尾（最后一个除外）加上"union all"，格式会更有意义。 - Jamie
5
这种方法的一个缺点是我们无法在 union select 中使用 sequnce.nextval，因为它是被禁止的。相反，我们可以使用 INSERT ALL。 - sql_dummy
6
@Jamie: Espo的格式略微更智能，因为在添加新行时无需担心是否处于最后一行。因此，一旦你有了前两个选择，你可以轻松地复制/粘贴最后一行（或中间的一行），只关注你需要更改的值。这是任何语言中的许多其他情况下的常见技巧（逗号、逻辑运算符、加号等）。这只是一个习惯问题，很多以前的做法已经被修正，以便更专注于代码的可靠性而不是直观性。 - Laurent.B
如果我需要插入序列的下一个值（sequence.nextval），该怎么办？我遇到了这个错误*02287. 00000 - “不允许使用序列号” 原因：在语句中指定的序列号（CURRVAL或NEXTVAL）不适用于此处。 - Rafael Andrade
显示剩余6条评论
35

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

我的表中的ID列是自动生成的。我是否可以在加载器控制文件中简单地跳过ID字段？ - Thom DeCarlo
@Thom，使用sequence.nextval，例如在列定义中使用fruit_id "fruit_seq.nextval"。 - roblogic
在几分钟内处理五千万条记录。干得好！ - Toolkit
28
每当我需要这样做时，我会构建一个简单的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;
/
这个答案非常好，特别是如果你需要使用序列。已点赞。 - diekunstderfuge
16

如果您已经有要插入到另一个表中的值，则可以使用选择语句进行插入。

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

否则，您可以列出一堆单行插入语句，并提交多个查询以批量保存时间，这对Oracle和MySQL都有效。 @Espo的解决方案也很好，如果您的数据尚未在表中，则适用于Oracle和MySQL。
8

如果您想插入一些随机值，可以使用循环进行插入。

BEGIN 
    FOR x IN 1 .. 1000 LOOP
         INSERT INTO MULTI_INSERT_DEMO (ID, NAME)
         SELECT x, 'anyName' FROM dual;
    END LOOP;
END;
-1
-1
在我的情况下，我能够使用一个简单的插入语句，仅使用TABLE_B中的一个列来批量插入多行数据到TABLE_A，同时从其他地方（序列和硬编码值）获取其他数据：
INSERT INTO table_a (
    id,
    column_a,
    column_b
)
    SELECT
        table_a_seq.NEXTVAL,
        b.name,
        123
    FROM
        table_b b;

结果：

ID: NAME: CODE:
1, JOHN, 123
2, SAM, 123
3, JESS, 123

等等

-1

这里有一个非常有用的逐步指南，可以在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
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接