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

But sometimes, it says this 

ORA-24335: cannot support more than 1000 columns

Any ideas ?

Mouhcine
  • 276
  • 1
  • 2
  • 12
  • I binged "oracle multi insert limit" ... couldn't find any limits on this kind of insert – Mouhcine Dec 03 '14 at 00:21
  • Did you Google the error? http://stackoverflow.com/a/8062870/1394393 (Oracle has really obscure problems like the one you have, in my experience.) – jpmc26 Dec 03 '14 at 00:23
  • you are mentioning that you inserted 10,100 **rows** but the ORA you are getting is about **columns** – benji Dec 03 '14 at 02:38
  • @benji You can't expect Oracle errors to actually make sense, sadly. – jpmc26 Dec 17 '14 at 17:46

2 Answers2

4

Yes, there is a limit:

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

You're inserting in a single table, so it can be done without multi table insert. For example, by using 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
...
Multisync
  • 8,657
  • 1
  • 16
  • 20
  • 4
    When inserting into a single table the limit does not seem to apply. At least not in 11g, I managed to insert 10K columns in total, without issue. – yktoo Jun 07 '16 at 12:20
0

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.

Compared with insert all statement, sql loader exactly will get you better results for inserting too many rows.

Ali Cihan
  • 11
  • 1