CODEWITHSUNDEEP
sqloracle-database
Dr.Avalanche
Dr.Avalanche

Reputation: 2006

Oracle - SQL - insert into with sub select

I'm getting ORA-00947: not enough values from the query below:

insert into tableb
(colA, colb, colc, cold)
select
(select max(rec_no)+1 from tableb)
F2,
F3,
F4
from tablea;

Can someone point me to the correct way to include a sub query for an inser into/select statement?

Thanks

Upvotes: 9

Views: 25054

Answers (2)

user330315
user330315

Reputation:

The only reliable, fast and scalable way to generate unique IDs is using sequences.

The reason why the max() "solution" won't work, is a transaction will not see uncommitted changes from another transaction. So two concurrent transactions can wind up using the same value for max() which in turn will generate duplicate id values.

To create the values from a sequence in your case, you obviously need to first create a sequence:

create sequence seq_b;

Then use that sequence in your select statement:

insert into tableb
  (colA, colb, colc, cold)
select seq_b.nextval,
       F2,
       F3,
       F4
from tablea;

Upvotes: 8

Thilo
Thilo

Reputation: 262842

You are just missing a comma. As it is, Oracle thinks F2 is the name of your sub-select.

insert into tableb
(colA, colb, colc, cold)
select
(select max(rec_no)+1 from tableb) ,   -- comma here
F2,
F3,
F4
from tablea;

Upvotes: 16

Related Questions