Hi Experts,

Can you please suggest me for the following scenario,

I have a main table with two columns id and date. I also have a history table with three columns seq_num,id and date.

Main table:

id date
1 Apr 5
1 Apr 6
2 Apr 6
3 Apr 7
2 Apr 7
1 Apr 8

When I insert the above rows into the history table, I want the sequence number for each group(based on the id). The history table should look like,

seq_num id date
1 1 Apr 5
2 1 Apr 6
3 1 Apr 8
1 2 Apr 6
2 2 Apr 7
1 3 Apr 7

So I want the sequence number to be populated for the each id group. Hope I am clear with my requirement.

Kindly suggest me a way to do the same.

Thanks in Advance,
Chella

Create procedure seq_idgen
As
Begin
declare @temp_id int
declate @hist_cnt int

select @hist_cnt = count(*) from history
if @hist_cnt > 0
truncate table history

declare idgen_crsr cursor
for select distinct id
from main_table order by id
for read only

create table #temp(seq_no int, id int, date varchar(20))

open idgen_crsr

fetch idgen_crsr into @temp_id

while (@@sqlstatus = 0)
begin

create table #temp1(seq_no int identity, id int, date varchar(20))

select * into #temp1 from main_table where id = @temp_id

insert #temp
select * from #temp1

drop table #temp1

fetch idgen_crsr into @temp_id
End

close idgen_crsr

deallocate cursor idgen_crsr

insert history
select * from #temp group by seq_no order by seq_no, id

drop table #temp
End

Thanks a lot for the reply.

Can you tell me if there is a way to achieve the same scenario without using cursors (looping).

Kindly suggest.


Regards,
Chella

Changes in bold are done as a replacement to cursor

Create procedure seq_idgen
As
Begin
declare @temp_id int
declare @hist_cnt int
declare @id_main int
declare @cnt int

select @hist_cnt = count(*) from history
if @hist_cnt > 0
truncate table history

create table #temp_iden(id int)

insert #temp_iden
select distinct id from main_table order by id
create table #temp(seq_no int, id int, date varchar(20))

set rowcount 1
select @id_main = id from #temp_iden
set rowcount 0
select @cnt = count(id) from #temp_iden
while (@cnt > 0)
begin

create table #temp1(seq_no int identity, id int, date varchar(20))

select * into #temp1 from main_table where id = @id_main

insert #temp
select * from #temp1

drop table #temp1

delete #temp_iden where id = @id_main
set rowcount 1
select @id_main = id from #temp_iden
set rowcount 0
select @cnt = count(id) from #temp_iden
End

insert history
select * from #temp group by seq_no order by seq_no, id

drop table #temp
drop table #temp_iden
End

instead of while loops