Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table final_table (fst_name,lst_name,state_code,row_nbr) as
select 'John', 'Doe', 13, 123 from dual;
1 rows affected

create table staging_table (fst_name,lst_name,state_code) as
select 'A', 'A', 13 from dual
union all
select 'B', 'B', 13 from dual
2 rows affected

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

select * from final_table;


FST_NAME
LST_NAME
STATE_CODE
ROW_NBR


John
Doe
13
123

A
A
13
null

B
B
13
null


rollback;
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;
2 rows affected

select * from final_table;


FST_NAME
LST_NAME
STATE_CODE
ROW_NBR


John
Doe
13
123

A
A
13
124

B
B
13
125