Inserting Row Number based on existing value in the table

Question

I have a requirement that I need to insert row number in a table based on value already present in the table. For example, the max row_nbr record in the current table is something like this:

+----------+----------+------------+---------+
| FST_NAME | LST_NAME | STATE_CODE | ROW_NBR |
+----------+----------+------------+---------+
| John     | Doe      |         13 |     123 |
+----------+----------+------------+---------+

Now, I need to insert more records, with given FST_NAME and LST_NAME values. ROW_NBR needs to be generated while inserting the data into table with values auto-incrementing from 123.

I can't use a sequence, as my loading process is not the only process that inserts data into this table. And I can't use a cursor as well, as due to high volume of data the TEMP space gets filled up quickly. And I'm inserting data as given below:

insert into final_table
( fst_name,lst_name,state_code)
(select * from staging_table
where state_code=13);

Any ideas how to implement this?

Why do you have this requirement, and what would be wrong with a standard auto increment column along with something like a timestamp column? — Tim Biegeleisen, Jun 14 '21 at 06:36
Why can't you just use a sequence? You mention that there are multiple processes inserting but this is exactly what sequences are optimised for. — doberkofler, Jun 14 '21 at 06:38
I can't use a sequence as all other processed are already inserting data into this table using cursors. And re-writing a tonne of code is not something I want to do, and I don't have access to alter existing code as well. And I can't change the existing table structure as well. — Abhinav Dhiman, Jun 14 '21 at 06:47
There's no such thing as a free lunch, I'm afraid. +1 for the *sequence* option. — Littlefoot, Jun 14 '21 at 07:16
What is the significance of other processes using cursors - are those finding the current maximum value and then incrementing inside a loop that does single-row inserts; and you could do the same but you want to (correctly) so a single `insert...select`? If so, you could still set the value within your statement, but it isn't ideal. How do you avoid clashes at the moment? Anyway... if you can't stop other processes trying to set `row_nbr` manually, you can still have a trigger that ignores the number they use and overrides it with a sequence value. — Alex Poole, Jun 14 '21 at 07:43

score 1 · Accepted Answer · answered Jun 14 '21 at 08:14

It sounds like other processes are finding the current maximum row_nbr value and incrementing it as they do single-row inserts in a cursor loop.

You could do something functionally similar, either finding the maximum in advance and incrementing it (if you're already running this in a PL/SQL block):

insert into final_table (fst_name, lst_name, state_code, row_nbr)
select st.*, variable_holding_maximum + rownum
from staging_table st
where st.state_code=13;

or by querying the table as part of the query, which doesn't need PL/SQL:

insert into final_table (fst_name, lst_name, state_code, row_nbr)
select st.*, (select max(row_nbr) from final_table) + rownum
from staging_table st
where st.state_code=13;

db<>fiddle

But this isn't a good solution because it doesn't prevent clashes from different processes and sessions trying to insert at the same time; but neither would the cursor loop approach, unless it is catching unique constraint errors and re-attempting with a new value, perhaps.

It would be better to use a sequence, which would be an auto-increment column but you said you can't change the table structure; and you need to let the other processes continue to work without modification. You can still do that with a sequence and trigger approach, having the trigger always set the row_nbr value form the sequence, regardless of whether the insert statement supplied a value.

If you create a sequence that starts from the current maximum, with something like:

create sequence final_seq start with <current max + 1>

or without manually finding it:

declare
  start_with pls_integer;
begin
  select nvl(max(row_nbr), 0) + 1 into start_with from final_table;
  execute immediate 'create sequence final_seq start with ' || start_with;
end;
/

then your trigger could just be:

create trigger final_trig
before insert on final_table
for each row
begin
  :new.row_nbr := final_seq.nextval;
end;
/

Then your insert ... select statement doesn't need to supply or even think about the row_nbr value, so you can leave it as you have it now (except I'd avoid select * even in that construct, and list the staging table columns explicitly); and any existing inserts that do supply the row_nbr don't need to be modified and the value they supply will just be overwritten from the sequence.

db<>fiddle showing inserts with and withouth row_nbr specified.

Inserting Row Number based on existing value in the table

1 Answers1