Oracle - SQL - insert using sub select

Question

I get ORA-00947: not enough values from the request below:

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

Can someone tell me the correct way to include a sub-query to insert into / select expression?

thanks

Answer 1

You just do not have enough commas. Be that as it may, Oracle believes that F2 is the name of your subsample.

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

Answer 2

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

The reason the max() "solution will not work is because the transaction will not see uncommitted changes from another transaction. Thus, two concurrent transactions can complete using the same value for max() , which in turn will generate duplicate id values.

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

 create sequence seq_b; 

Then use this sequence in the select statement:

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