-5
INSERT INTO EMP_COMPANY(ename,cname,salary,jdate) VALUES
('ANIL','ACC',1500.00,'01-MAY-89'),
........
('AMOL','ACC',1000.00,'17-MAR-95');

Error: ORA-00933: SQL command not properly ended . what is missing in syntax ?

Himanshu
  • 13
  • 6
  • I think Oracle only supports inserting one row using `values()`. Use `select . . . from dual union all` for multiple rows. – Gordon Linoff Dec 17 '15 at 12:13
  • You're not asking any clear question. CAPS IN TITLE IS BAD STYLE. You don't show any attempt at solving. – Marcus Müller Dec 17 '15 at 12:14
  • I tried but it is not working . i highlighted the error which that oracle 10g ex is showing... – Himanshu Dec 17 '15 at 12:17
  • ok @GordonLinoff let m try your way. – Himanshu Dec 17 '15 at 12:19
  • 2
    Possible duplicate of [I'm getting an error in SQL command not properly ended](http://stackoverflow.com/questions/17288408/im-getting-an-error-in-sql-command-not-properly-ended) – Colselaw Dec 17 '15 at 12:20
  • Oracle and Mysql are different database and have different syntax. Dont mix them! Your query will work on MySQL but is not a proper syntax for Oracle. – Rahul Tripathi Dec 17 '15 at 12:42

1 Answers1

2

You cannot multiple records like the way you are doing. You can try like this:

INSERT INTO EMP_COMPANY(ename,cname,salary,jdate) 
select 'ANIL','ACC',1500.00,'01-MAY-89' from dual
union all 
select 'SHANKAR','TATA',2000.00,'10-MAY-90' from dual
union all
select 'JAYA','CMC',1800.00,'7-JULY-91' from dual
union all
select 'SUNIL','CMC',1700.00,'1-JAN-88' from dual
union all
select 'VIJAY','TATA',5000.00,'3-JAN-88' from dual
union all
select 'PRAKASH','TATA',3000.00,'27-MAY-89' from dual

There is one more option to use INSERT ALL like this:

   INSERT ALL
   INTO EMP_COMPANY (ename,cname,salary,jdate) VALUES ('ANIL','ACC',1500.00,'01-MAY-89')
   INTO EMP_COMPANY  (ename,cname,salary,jdate) VALUES ('SHANKAR','TATA',2000.00,'10-MAY-90')
   INTO EMP_COMPANY  (ename,cname,salary,jdate) VALUES ('JAYA','CMC',1800.00,'7-JULY-91')
   ...........
   SELECT 1 FROM DUAL;
Rahul Tripathi
  • 168,305
  • 31
  • 280
  • 331
  • thank you for other way.. but i want to tell me ..is it(insertion of multiple row) possible in way i shown it above – Himanshu Dec 17 '15 at 12:21
  • @Himanshu:- No that is not the correct syntax for inserting multiple records. – Rahul Tripathi Dec 17 '15 at 12:21
  • i am working on your syntax ..what does "SELECT 1 FROM DUAL "means here . ? but this is alternate way to solve the query .. my syntax doesnt work in oracle database ex .. @rahul tripathi – Himanshu Dec 17 '15 at 12:34
  • @Himanshu:- As I said, your syntax is not correct. As far as `select 1 from dual` is concerned then you can understand it like this that INSERT ALL requires the select query. – Rahul Tripathi Dec 17 '15 at 12:36
  • yes your are right . there is syntax error .. – Himanshu Dec 17 '15 at 12:44