CODEWITHSUNDEEP
sqlteradata
Kaleem
Kaleem

Reputation: 55

SQL, How to put incremental row number during append?

I have table in which i have data like 

date       id   $   row_nmbr
1/1/2016    A   40  1
1/2/2016    A   40  2
1/3/2016    A   40  3
1/1/2016    B   40  1
1/2/2016    B   40  2

This is working fine when i insert a new set of data, But when i insert data for a existing combination i am getting the row number back to 1.

new data 

date        id  $   
1/4/2016    A   40

When is insert it i want it to get inserted like 

date       id   $   row_nmbr
1/1/2016    A   40  1
1/2/2016    A   40  2
1/3/2016    A   40  3
1/4/2016    A   40  4<--- New data
1/1/2016    B   40  1
1/2/2016    B   40  2

Help on how this can be achieved during next inserts

Upvotes: 0

Views: 1165

Answers (2)

dnoeth
dnoeth

Reputation: 60513

This will increase the row numbers for existing ids and start with one for new ids:

insert into target_table
select src.date, src.id, src.$, 
   row_number() over (partition by src.id order by src.date)
     -- existing number or zero for new ids
   + coalesce(row_nmbrs.max_row_nmbr, 0)
from source_table as src
left join 
 ( -- current row numbers for each id
   select id, max(row_nmbr) as max_row_nmbr
   from target_table
   group by 1
 ) as row_nmbrs
on src.id = row_nmbrs.id

Upvotes: 1

Mike
Mike

Reputation: 2005

insert into Table(date, id, `$`, row_nmbr)
  select '01-04-2016', 'A', 40, coalesce(max(row_nmbr),0)+1
    from Table
   where id='A'

Upvotes: 0

Related Questions