ORACLE 的 Multi Insert 有限制吗？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27261494/
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
Is there a limit to ORACLE's Multi Insert?
提问by Mouhcine
INSERT ALL
INTO t (col1, col2, col3) VALUES ('val1_1', 'val1_2', 'val1_3')
INTO t (col1, col2, col3) VALUES ('val1_1', 'val1_2', 'val1_3')
.......
INTO t (col1, col2, col3) VALUES ('val1_1', 'val1_2', 'val1_3')
SELECT * FROM DUAL;
I'm using this statement to insert multiple rows into a table.
我正在使用此语句将多行插入到表中。
Sometimes I inserts correctly, even if I give it a couple of thousands of lines. I even inserted 10100 lines with it!
有时我会正确插入，即使我给它几千行。我什至用它插入了 10100 行！
But sometimes, it says this
但有时，它会这样说
ORA-24335: cannot support more than 1000 columns
Any ideas ?
有任何想法吗 ？
回答by Multisync
Yes, there is a limit:
是的，有一个限制：
https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_9014.htm
https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_9014.htm
Restrictions on Multitable Inserts
Multitable inserts are subject to the following restrictions:
...
- In a multitable insert, all of the insert_into_clauses cannot combine to specify more than 999 target columns.
...
对多表插入限制
多表插入受到以下限制：
......
-在一个多表插入，所有的insert_into_clauses不能结合起来，指定999个以上的目标列。
...
You're inserting in a single table, so it can be done without multi table insert. For example, by using UNION ALL:
您在单个表中插入，因此无需多表插入即可完成。例如，通过使用 UNION ALL：
insert into t (col1, col2, col3)
select 'val1_1', 'val1_2', 'val1_3' from dual
union all
select 'val1_1', 'val1_2', 'val1_3' from dual
...
回答by Ali Cihan
I tried to insert 50.000 rows with one insert all statement but my editor (oracle sql developer) could not handle it. I didn't get any errors but after a few seconds it didn't respond. Then I tried to insert fewer rows starting at 500. Nearly at 5000 rows of one insert all statement, my editor failed again. I also didn't see any limits on documents. Then I tried another method for inserting large number of rows. I splitted my whole 50.000 row statement into 1000 rows of "insert all" statements (using a small macro script). All inserts was successfully done and total execution time was fair.
我试图用一个 insert all 语句插入 50.000 行，但我的编辑器（oracle sql 开发人员）无法处理它。我没有收到任何错误，但几秒钟后它没有响应。然后我尝试从 500 开始插入更少的行。在一个 insert all 语句的将近 5000 行时，我的编辑器再次失败。我也没有看到对文件的任何限制。然后我尝试了另一种插入大量行的方法。我将整个 50.000 行语句拆分为 1000 行“插入所有”语句（使用一个小的宏脚本）。所有插入都成功完成，总执行时间是公平的。
Compared with insert all statement, sql loader exactly will get you better results for inserting too many rows.
与 insert all 语句相比，sql loader 确实会在插入过多行时获得更好的结果。
