Batch SQL Insert is Failing

Question

I am trying to execute a batch insert as below:

SQL> INSERT INTO Z (q, w, e, r) 
VALUES
('7', 'A', '3', 'A'),
('8', 'B', '4', 'An'),
('9', 'C', '5', 'And');

                           *
ERROR at line 1:
ORA-00933: SQL command not properly ended

However it keeps failing. Could someone help me out why? Thanks!

Since you're using Oracle, that's invalid syntax. See here: http://stackoverflow.com/questions/39576/best-way-to-do-multi-row-insert-in-oracle — sgeddes, Mar 05 '15 at 15:14
You can't specify multiple rows with a single `insert` statement in Oracle - something which is clearly documented in the manual. — , Mar 05 '15 at 15:16
@AngryPanda you're making drastic edits to your question. Have you tried any of the answers posted? — Dr.Avalanche, Mar 05 '15 at 15:26
@Dr.Avalanche: I should have added "using the `values` clause" — , Mar 05 '15 at 15:27

score 0 · Answer 1 · answered Mar 05 '15 at 15:18

You can do this with one statement if you use insert . . . select:

INSERT INTO cellphone (phone_id, model, data, os) 
    SELECT '99997', 'Galaxy S IV', '4g', 'Android' FROM DUAL UNION ALL
    SELECT '99998', 'Galaxy S V', '4g', 'Android' FROM DUAL UNION ALL
    SELECT '99999', 'Galaxy S VI', '4g', 'Android' FROM DUAL;

score 0 · Accepted Answer · answered Mar 05 '15 at 15:19

0

INSERT INTO cellphone (phone_id, model, data, os) 
select '99997', 'Galaxy S IV', '4g', 'Android' from dual
union all select '99998', 'Galaxy S V', '4g', 'Android' from dual

answered Mar 05 '15 at 15:19

Dr.Avalanche

1,944
2
28
37

Philip H. · Answer 3 · 2015-03-05T15:35:15.967

The Problem is: Oracle does´t support the batch insert syntax like its implementet in MySQL.

Ther are a few ways how you can achive your goal. (But i have to admit the MySQL bulk insert Syntax styl would be nice to have in ODBMS.)

Next to the also mententioned "union all select from dual" you could take a look at:

Load it with the SQL*Loader from a CSV File. If you realy want to add a lot of entrys i suggest this.

To speed insertation with Single writen Insert Statments use "commit write batch" so Oracle will write the Statements in Batches. (If you also want to reduce the ammount of copied/written "INSERT INTO cellphone VALUES" Code you could write a PL/SQL Procedure so you only have to copy the call of the procedure.)

Ther a planty of good tutorials for the SQL*Loader, PL/SQL and Procedures if you still have Questions feel free to ask. I can add code examples if needed.

Batch SQL Insert is Failing

3 Answers3