8

I created a table in Oracle SQL :

create table t1
 (
 empno number(6) PRIMARY KEY,
 empname varchar(30),
 hiredate date,
 basic number(8),
 deptno number(4)
);

And now I am inserting values into the table using a single query:

insert into t1 values((131309,'HP','20-FEB-04',2000000,1235)
(131310,'HT','20-APR-14',120020,1234));

But this shows error:

insert into t1 values((131309,'HP','20-FEB-04',2000000,1235),
                             *
ERROR at line 1:
ORA-00907: missing right parenthesis

How do I correct this?

h8pathak
  • 1,342
  • 3
  • 17
  • 29
  • Possibly a duplicate of [Best way to do multi-row insert in Oracle?](https://stackoverflow.com/questions/39576). – Bass May 19 '17 at 12:58

1 Answers1

19

An INSERT VALUES statement always inserts exactly 1 row. If you want to insert multiple rows with hard-coded values, the most common approach would simply be to execute two separate INSERT statements.

insert into t1 values(131309,'HP','20-FEB-04',2000000,1235);
insert into t1 values(131310,'HT','20-APR-14',120020,1234);

If you really wanted to, you could select your hard-coded values from dual and then do an INSERT SELECT

insert into t1
  select 131309, 'HP', '20-FEB-04',2000000,1235 from dual
  union all
  select 131310,'HT','20-APR-14',120020,1234 from dual

Or you could do an INSERT ALL

insert all 
  into t1 values(131309,'HP','20-FEB-04',2000000,1235)
  into t1 values(131310,'HT','20-APR-14',120020,1234)
  select * from dual

Personally, I'd just use two statements.

Although this isn't related to your question, a couple of comments

  • Always, always list out the columns in your insert statement. You'll make your SQL much more robust so that if you add new columns in the future that allow NULL values your statements will still work. And you'll avoid lots of bugs when the column list is right there rather than hoping that someone remembers the order of columns in the table.
  • If you're inserting a value into a date column, use a date not a string literal that represents a date. Relying on implicit data type conversion is a source of many bugs. Use an explicit to_date or use ANSI date literals. And use 4-digit years.
Justin Cave
  • 227,342
  • 24
  • 367
  • 384
  • insert all * ERROR at line 1: ORA-00001: unique constraint (SCOTT.SYS_C005813) violated – h8pathak Sep 05 '14 at 07:02
  • 1
    Is `SYS_C005813` your primary key constraint? If so, that would imply that you already have a row in `t1` with at least one of the two `empno` values you are trying to insert. You'd get that error no matter how you tried to insert the rows. – Justin Cave Sep 05 '14 at 07:04