Oracle - SQL - insert into with sub select
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;