Geek Answers Handbook

How to insert multiple rows into one 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 insert the values ​​into the table with a single query :

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

But this shows an error:

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

How to fix it?

+4
source share
1 answer

The instruction INSERT VALUESalways inserts exactly 1 row. If you want to insert multiple lines with hard-coded values, the most common approach would be to simply execute two separate statements INSERT.

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 want to, you can select your hard-coded values ​​from dual, and then doINSERT 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 can do 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

.

,

  • , INSERT. SQL , , NULL, . , , , - .
  • date, , , . . to_date ANSI. 4- .
+10

More articles:


All Articles