Oracle SQL issue while inserting multiple rows into a table

Question

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"

JodyT · Accepted Answer · 2015-04-06 12:57:56Z

3

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;

answered Apr 6, 2015 at 12:57

JodyT

4,4222 gold badges22 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gordon Linoff · Accepted Answer · 2015-04-06 12:56:02Z

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.

koushik veldanda · Accepted Answer · 2015-04-06 13:06:58Z

1

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

answered Apr 6, 2015 at 13:06

koushik veldanda

1,10710 silver badges23 bronze badges

Collectives™ on Stack Overflow

Oracle SQL issue while inserting multiple rows into a table

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related