Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

I'm getting an error in SQL command not properly ended

when I enter this

INSERT INTO works_on
(essn, pno, hours)
values
('123456789', 1, 32.5),
('123456789', 2, 7.5),
('666884444', 3, 40.0),
('453453453', 1, 20.0),
('453453453', 2, 20.0),
('333445555', 2, 10.0),
('333445555', 3, 10.0),
('333445555', 10, 10.0),
('333445555', 20, 10.0),
('999887777', 30, 30.0),
('999887777', 10, 10.0),
('987987987', 10, 35.0),
('987987987', 30, 5.0),
('987654321', 30, 20.0),
('987654321', 20, 15.0),
('888665555', 20, 0);

I get the follow error

ORA-00933: SQL command not properly ended

like image 861
UnPatoCuacCuac Avatar asked Jun 25 '13 02:06

UnPatoCuacCuac


1 Answers

In Oracle, you can't specify multiple sets of values like this (I'm guessing that you're coming from a MySQL background where that syntax is allowed). The simplest approach is to generate multiple INSERT statements

INSERT INTO works_on
(essn, pno, hours)
values
('123456789', 1, 32.5);

INSERT INTO works_on
(essn, pno, hours)
values
('123456789', 2, 7.5);

INSERT INTO works_on
(essn, pno, hours)
values
('666884444', 3, 40.0);

...

You can also insert multiple rows using a single INSERT ALL statement

INSERT ALL
  INTO works_on(essn, pno, hours) values('123456789', 1, 32.5)
  INTO works_on(essn, pno, hours) values('123456789', 2, 7.5)
  INTO works_on(essn, pno, hours) values('666884444', 3, 40.0)
  INTO works_on(essn, pno, hours) values('453453453', 1, 20.0)
  INTO works_on(essn, pno, hours) values('453453453', 2, 20.0)
  INTO works_on(essn, pno, hours) values('333445555', 2, 10.0)
  INTO works_on(essn, pno, hours) values('333445555', 3, 10.0)
  ...
SELECT *
  FROM dual;
like image 112
Justin Cave Avatar answered Oct 14 '22 00:10

Justin Cave