Oracle - SQL - insert into with sub select Oracle - SQL - insert into with sub select sql sql

Oracle - SQL - insert into with sub select

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 hereF2,F3,F4from tablea;

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,       F4from tablea;