0

I am trying to insert MULTIPLE rows into a table at a time using select as following

insert into myTable values(myTable_Seq.nextval,100,select column1 from anotherTable where column1 not in(10,20));

Here my last value is selecting values from another table with a where condition.

I am failing here.It's giving missing expression error.

Can we do like this or is it a must to do a forloop.

Thanks in advance.

Santosh
  • 2,355
  • 10
  • 41
  • 64
  • There are no shortages of examples on how to do this on stackoverflow (see the answer by Ryan here, https://stackoverflow.com/questions/7323407/insert-select-statement-in-oracle-11g). – Patrick Bacon Jun 02 '17 at 17:56

2 Answers2

3

You don't need the VALUES clause if you are inserting from a SELECT.

INSERT INTO mytable
  SELECT mytable_seq.nextval,
         100,
         column1
    FROM anothertable
   WHERE column1 NOT IN (10,
                         20);

It is a best practice to list the columns in the destination table. This allows new ones to be added, that are NULLable, without disturbing existing DML.

INSERT INTO mytable
  (col1,
   col2,
   col3)
  SELECT mytable_seq.nextval,
         100,
         column1
    FROM anothertable
   WHERE column1 NOT IN (10,
                         20);
Kevin Seymour
  • 766
  • 9
  • 25
1

You need to remove values and use the sequence value and the fixed value in the query from anotherTable:

insert into myTable
select myTable_Seq.nextval,
       100,
       column1
from anotherTable
where column1 not in(10,20)
Aleksej
  • 22,443
  • 5
  • 33
  • 38