CODEWITHSUNDEEP
oracle-database
Santosh
Santosh

Reputation: 2495

INSERT INTO USING SELECT IN ORACLE

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.

Upvotes: 0

Views: 8188

Answers (2)

Kevin Seymour
Kevin Seymour

Reputation: 807

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

Upvotes: 5

Aleksej
Aleksej

Reputation: 22949

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)

Upvotes: 1

Related Questions