CODEWITHSUNDEEP
sqloracle-database
user4378716
user4378716

Reputation:

Insert multiple row in sql

In Oracle Application Express. I created a table named Employees. I want to insert multiple rows values by a single command . I used this command 

INSERT INTO Employees
VALUES(
(100,'Steven','King','SKING','515.123.4567',TO_DATE('06-17-1987','MM- DD-YYYY'),'AD_PRESS',24000,NULL,NULL,90),
(101,'Neena','Kochar','NKOCHHAR','515.123.4568',TO_DATE('09-21-1989','MM-DD-YYYY'),'AD_VP',17000,NULL,100,90),
);

and I got ORA-00907: missing right parenthesis error . Where is my mistake ? Thnx in advance.

Upvotes: 1

Views: 344

Answers (3)

davegreen100
davegreen100

Reputation: 2115

you need to do an INSERT ALL

http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_9014.htm

INSERT ALL 
  INTO Employees (cols) values (100,'Steven','King','SKING','515.123.4567',TO_DATE('06-17-1987','MM- DD-YYYY'),'AD_PRESS',24000,NULL,NULL,90)
  INTO Employees (cols) values (101,'Neena','Kochar','NKOCHHAR','515.123.4568',TO_DATE('09-21-1989','MM-DD-YYYY'),'AD_VP',17000,NULL,100,90)
select * from dual

Upvotes: 0

Praveen
Praveen

Reputation: 9365

This is not a valid syntax in Oracle;

To insert multiple rows one can use union all or insert all

insert into tbl (
select val1, val2 from dual
union all
select val2, val4 from dual 
....
);

or

insert all 
into tbl(clm1, clm2) values (val1, val2)
into tbl(clm1, clm2) values (val3, val4)
....
select 1 from dual;

Upvotes: 1

SmartDev
SmartDev

Reputation: 2872

Try this:

INSERT INTO Employees
select 100,'Steven','King','SKING','515.123.4567',TO_DATE('06-17-1987','MM- DD-YYYY'),'AD_PRESS',24000,NULL,NULL,90
union select 101,'Neena','Kochar','NKOCHHAR','515.123.4568',TO_DATE('09-21-1989','MM-DD-YYYY'),'AD_VP',17000,NULL,100,90

Upvotes: 0

Related Questions