INSERT INTO USING SELECT IN ORACLE

Question

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.

Answer 1

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);

Answer 2

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)