Inserting Row Number based on existing value in the table

user_13595636 0
Viewed 145

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?

plsqldeveloper plsql oracle
1 Answers

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.

user_2663040
Related