Inserting multiple rows query failed [duplicate]

Question

INSERT INTO EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)
VALUES  (7499,'ALLEN','SALESMAN',7698,'20-Feb-81',1600,300,30)
      , (7521,'WARD','SALESMAN',7698,'22-Feb-81',1250,500,30);

Tried to insert two rows at the same time. but failed saying "SQL command not properly ended". Can someone please correct the query?

Error:

Error at Command Line : 18 Column : 125 Error report - SQL Error: ORA-00933: SQL command not properly ended 00933. 00000 - "SQL command not properly ended" *Cause: *Action:

@Gaurav INSERT INTO EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) VALUES (7499,'ALLEN','SALESMAN',7698,'20-Feb-81',1600,300,30),(7521,'WARD','SALESMAN',7698,'22-Feb-81',1250,500,30) Error at Command Line : 18 Column : 125 Error report - SQL Error: ORA-00933: SQL command not properly ended 00933. 00000 - "SQL command not properly ended" *Cause: *Action: — beyou
– beyou, Commented Sep 5, 2019 at 6:33

Arulkumar · Accepted Answer · 2019-09-05 06:32:55Z

3

Based on your error message (ORA-00933:SQL command not properly ended), the DBMS is Oracle.

You can use the following query to INSERT INTO in Oracle.

INSERT ALL  
  INTO EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) VALUES (7499,'ALLEN','SALESMAN',7698,'20-Feb-81',1600,300,30)  
  INTO EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) VALUES (7521,'WARD','SALESMAN',7698,'22-Feb-81',1250,500,30)

answered Sep 5, 2019 at 6:32

Arulkumar

13.3k14 gold badges51 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

beyou Over a year ago

: getting error INSERT ALL INTO EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) VALUES (7499,'ALLEN','SALESMAN',7698,'20-Feb-81',1600,300,30) INTO EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) VALUES (7521,'WARD','SALESMAN',7698,'22-Feb-81',1250,500,30) Error at Command Line : 22 Column : 118 Error report - SQL Error: ORA-00928: missing SELECT keyword 00928. 00000 - "missing SELECT keyword"

William Robertson Over a year ago

It's missing the select statement required at the end. See documentation for insert.

Zaynul Abadin Tuhin · Accepted Answer · 2019-09-05 06:39:18Z

2

try like below

insert into emp  (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)

select 7499,'ALLEN','SALESMAN',7698,'20-Feb-81',1600,300,30  from dual
union all
select 7521,'WARD','SALESMAN',7698,'22-Feb-81',1250,500,30 from dual

answered Sep 5, 2019 at 6:39

Zaynul Abadin Tuhin

32.1k6 gold badges37 silver badges66 bronze badges

2 Comments

beyou Over a year ago

It worked... THANKS

Zaynul Abadin Tuhin Over a year ago

@beyou mark it as accepted if it helps

DarkRob · Accepted Answer · 2019-09-05 06:37:01Z

1

To insert multiple records in ORACLE, you need to club your records into cte. Or use Insert All as mentioned by @Arulkumar.

INSERT INTO Emp (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) 
  WITH names AS ( 
    SELECT 7499,'ALLEN','SALESMAN',7698,'20-Feb-81',1600,300,30 UNION ALL 
    SELECT 7521,'WARD','SALESMAN',7698,'22-Feb-81',1250,500,30 
  ) 
  SELECT * FROM names

You may find this link for more info on insert command in Oracle.Link

answered Sep 5, 2019 at 6:37

DarkRob

3,8231 gold badge12 silver badges28 bronze badges

Comments

Final'Cie · Accepted Answer · 2019-09-05 09:04:57Z

1

Your Syntax is for Microsoft SQL Server, but your Error Message is from an Oracle DBMS.

You could use the INSERT ALL query:

INSERT ALL
  INTO mytable (column1, column2, column_n) VALUES (expr1, expr2, expr_n)
  INTO mytable (column1, column2, column_n) VALUES (expr1, expr2, expr_n)
  INTO mytable (column1, column2, column_n) VALUES (expr1, expr2, expr_n)
SELECT * FROM dual;

See Official Oracle Documentation

edited Sep 5, 2019 at 9:04

answered Sep 5, 2019 at 6:39

Final'Cie

931 silver badge9 bronze badges

Collectives™ on Stack Overflow

Inserting multiple rows query failed [duplicate]

4 Answers 4

2 Comments

2 Comments

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

2 Comments

Comments

Comments

Linked

Related