Using a sequence on an existing table

Question

I've got a table that people have been inserting into getting the primary key by doing a

SELECT max(id)+1 from table_a;

I want to add some records to that table using a INSERT INTO table_a SELECT ... FROM table_b, table_c ... simple SQL script, and I'm wondering how to generate the primary keys. My first thought was to create a temporary sequence, but Oracle evidently doesn't have a select setval to set the first value. So how do I get the current value of max(id)+1 to set the "start with" parameter to my sequence?

I found something on-line that I thought would work:

COLUMN S new_value st select max(id)+1 S from  table_a;
CREATE SEQUENCE cra_seq start with &st;

But it doesn't actually use st in the CREATE SEQUENCE but instead prompts me to enter it, which isn't what I need.

Answer 1

You can use the row_number analytical function to generate row numbers (1 through N).

Before you do the insert, get the max id that is in the table, and then add the row number to that max and it will populate your table correctly.

Answer 2

Is this something like what you want?

  1  declare
  2    id  integer;
  3  begin
  4    select max(rownum)+1 into id from dual;
  5    execute immediate 'create sequence myseq start with '||TO_CHAR(id);
  6* end;
  7  /

Answer 3

Couldn't you use the row_number function like so:

Insert Destination( Id, ...)
Select row_number() over( order by TableA.Col1... ) + MaxDestination.MaxId + 1 Num
 , ....
From TableA, TableB,...
 Cross Join ( Select Max(Id) MaxId From Destination ) MaxDestination