CODEWITHSUNDEEP
sqloracle-database
ZEE
ZEE

Reputation: 5869

Oracle SQL issue while inserting multiple rows into a table

I'm using the following query to insert multiple rows into a table my_employee :

insert all into 
  my_employee (id, last_name, first_name, userid, salary) values (&id, '&last_name', '&firstname', '&userid', &salary)
  my_employee (id, last_name, first_name, userid, salary) values (&id, '&last_name', '&firstname', '&userid', &salary)
  my_employee (id, last_name, first_name, userid, salary) values (&id, '&last_name', '&firstname', '&userid', &salary)
select * from DUAL;

yet I followed similar questions step by stet ,but I don't know why I'm getting this error : 

Erreur SQL : ORA-00928: missing SELECT keyword
00928. 00000 -  "missing SELECT keyword"

Upvotes: 1

Views: 2640

Answers (3)

koushik veldanda
koushik veldanda

Reputation: 1107

IN MSSQL

insert into my_employee (id, last_name, first_name, userid, salary)
    values (&id, '&last_name', '&firstname', '&userid', &salary)
go 100;

it will inserts 100 rows with same value

Upvotes: 1

JodyT
JodyT

Reputation: 4412

You need to specify into for each insert. This should work:

insert all 
  into my_employee (id, last_name, first_name, userid, salary) values (&id, '&last_name', '&firstname', '&userid', &salary)
  into my_employee (id, last_name, first_name, userid, salary) values (&id, '&last_name', '&firstname', '&userid', &salary)
  into my_employee (id, last_name, first_name, userid, salary) values (&id, '&last_name', '&firstname', '&userid', &salary)
select * from DUAL;

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1270873

You can do this with three insert statements:

insert into my_employee (id, last_name, first_name, userid, salary)
    values (&id, '&last_name', '&firstname', '&userid', &salary);

insert into my_employee (id, last_name, first_name, userid, salary)
    values (&id, '&last_name', '&firstname', '&userid', &salary);

insert into my_employee (id, last_name, first_name, userid, salary)
    values (&id, '&last_name', '&firstname', '&userid', &salary);

Or with one statement using union all:

insert into my_employee (id, last_name, first_name, userid, salary)
    select &id, '&last_name', '&firstname', '&userid', &salary
    from dual union all
    select &id, '&last_name', '&firstname', '&userid', &salary
    from dual union all
    select &id, '&last_name', '&firstname', '&userid', &salary;

I don't see an application for insert all because you want to insert into a single table.

Upvotes: 1

Related Questions