Showing results for 
Search instead for 
Did you mean: 

using sequence in insert query

Frequent Advisor

using sequence in insert query

Hi all,

i have a (staging)table say st_prod_tab having 3 columns testid prod_number price

i am using a sequence to autoincrement the "testid" in the sql loader control file and prod_number,price are loaded into the st_prod_tab from a flat file.

I am inturn doing some comparison and iserting records from the staging table to the operation table(Op_prod_tab).

Say i load 100 records from flat file into the staging table (this 100 records are later inserted into the operational table) today.

Now tomorrow i am loading say 300 records into the staging table and 500records day after and so on...

now how do i insert into the main table comparing data in the staging table starting from the last +1 sequence id?

insert into Op_prod_tab (
select prod_number,price
from st_pro_table s
where not exists (select 1
from op_prod_table o
where o.prod_number=s.prod_number
and (here i need to use the last+1 sequence id number so that the comparison is faster)