CODEWITHSUNDEEP
sqloracle-databaserdbms
h8pathak
h8pathak

Reputation: 1382

How to insert multiple rows in the same table-Oracle 10g

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?

Upvotes: 8

Views: 27726

Answers (1)

Justin Cave
Justin Cave

Reputation: 231861

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.

Upvotes: 19

Related Questions