SQL 向 Oracle 中插入多行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/883706/
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
Inserting multiple rows into Oracle
提问by user62564
In the discussionabout multiple row insert into the Oracle two approaches were demonstrated:
在关于多行插入 Oracle的讨论中，演示了两种方法：
First:
第一的：
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
Second:
第二：
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;
Could anyone argue the preference of using one over another?
任何人都可以争论使用一种而不是另一种的偏好吗？
P.S. I didn't do any research myself (even explanation plan), so any information or opinion would be appreciated.
PS我自己没有做任何研究（甚至解释计划），所以任何信息或意见将不胜感激。
Thanks.
谢谢。
回答by Quassnoi
From performance's point of view, these queries are identical.
从性能的角度来看，这些查询是相同的。
UNION ALLwon't hurt performance, since Oracleestimates the UNION'ed query only when it needs it, it doesn't cache the results first.
UNION ALL不会损害性能，因为仅在需要时才Oracle估计UNION'ed 查询，因此它不会首先缓存结果。
SELECTsyntax is more flexible in that sense that you can more easuly manupulate the SELECTquery if you want to change something.
SELECT从这个意义上说，语法更灵活，SELECT如果您想更改某些内容，您可以更轻松地操作查询。
For instance, this query:
例如，这个查询：
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
can be rewritten as
可以改写为
INSERT
INTO pager (PAG_ID,PAG_PARENT,PAG_NAME,PAG_ACTIVE)
SELECT 7999 + level, 0, 'Multi ' || 7999 + level, 1
FROM dual
CONNECT BY
level <= 2
By replacing 2with appropriate number, you can get any number of rows you want.
通过替换2为适当的数字，您可以获得所需的任意数量的行。
In case of INSERT ALL, you would have to duplicate the destination table description, which is less readable if you need, say, 40rows.
在 的情况下INSERT ALL，您必须复制目标表描述，如果您需要，例如40行，则可读性较差。
回答by Maciek
The INSERT ALLmethod has a problem with inserting bigger number of rows into a table.
该INSERT ALL方法在向表中插入更多行时存在问题。
I recently wanted to insert 1130 rows into a table with single SQL statement. When I tried to do this with INSERT ALLmethod I got the following error:
我最近想用单个 SQL 语句将 1130 行插入到一个表中。当我尝试使用INSERT ALL方法执行此操作时，出现以下错误：
ORA-24335 - cannot support more than 1000 columns
ORA-24335 - 不能支持超过 1000 列
When I used INSERT INTO .. UNION ALL ..approach everything went fine.
当我使用INSERT INTO .. UNION ALL ..方法时，一切都很好。
Btw. I didn't know about the UNION ALL method before I found this discussion :)
顺便提一句。在我发现这个讨论之前，我不知道 UNION ALL 方法:)
回答by user2753683
i tried some test and the faster solution should be
我尝试了一些测试，更快的解决方案应该是
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
buffering between 300 <-> 400 rows (i tried with odbc, this value could depends about its configuration)
在 300 <-> 400 行之间缓冲（我尝试使用 odbc，这个值可能取决于它的配置）
回答by Stephen ODonnell
I would suspect solution 1 is a bit of a hack that works and is probably less efficient than the designed alternative of Insert ALL.
我怀疑解决方案 1 是一种有效的 hack，并且可能比 Insert ALL 的设计替代方案效率低。
Insert all is really designed for you to insert many rows into more than 1 table as a result of a select, eg:
全部插入实际上是为您设计的，作为选择的结果，将多行插入到 1 个以上的表中，例如：
Insert ALL
into
t1 (c1, c2) values (q1, q2)
t2 (x1, x2) values (q1, q3)
select q1, q2, q3 from t3
If you want to load thousands of rows and they are not in the database already, I don't think this is the best way to do it - If your data is in a file, you want to look at External Tables or SQL Loader to efficiently insert the rows for you.
如果您想加载数千行并且它们已经不在数据库中，我认为这不是最好的方法 - 如果您的数据在文件中，您想查看外部表或 SQL 加载器有效地为您插入行。
回答by Kosi2801
The statement utilizing the UNION ALLhas theoretically a small performance disadvantage as it has to union the results of all statements before the insert can happen.
The INSERT ALLdoesn't have this disadvantage as the final result can already be processed line-by-line.
使用 的语句UNION ALL理论上有一个小的性能劣势，因为它必须在插入之前合并所有语句的结果。在INSERT ALL没有这个缺点，因为最终的结果已经可以处理的行由行。
But practically the optimizer inside Oracle should make the difference negligible and it is up to your preferences which way you choose.
但实际上，Oracle 内部的优化器应该可以忽略不计，这取决于您选择哪种方式的偏好。
In my own opinion the INSERT ALLis the better human-readable of the two while the UNION ALLvariant is the one taking less space when such an insert is automatically generated.
在我自己看来INSERT ALL，这两者中人类可读性更好，而UNION ALL变体在自动生成这样的插入时占用的空间更少。
回答by Naresh
If you have insert statements that are more than 1000 then put all the insert statements in a .sql file and open that in Toad or SQL Developer and then execute. All records will get inserted.
如果您有超过 1000 条的插入语句，则将所有插入语句放在一个 .sql 文件中，并在 Toad 或 SQL Developer 中打开该文件，然后执行。所有记录都将被插入。
回答by AlterHase61
You should consider Array-Insert.
你应该考虑数组插入。
- Easy SQL
- need some client-side coding to setup the array-Parameters
- 简单的 SQL
- 需要一些客户端编码来设置数组参数
This is the way to minimize the Network-Traffic if some hundred inserts needs to be done in a batch.
如果需要批量完成数百次插入，这是最小化网络流量的方法。
