Hi,
  Try This..Create Two Procedures and fire the first one I named it test...the second procedure automatically gets fired from the first one.

First Procedure

Create procedure test
as
declare curr_count cursor
for
Select MemberReference from TableName group by MemberReference
declare
@ref as numeric
begin
open Curr_count
fetch next from curr_count into @ref
while @@fetch_status=0
begin
exec testInsert @ref
fetch next from curr_count into @ref
end
close curr_count
deallocate curr_count
end

Second Procedure

Create procedure testInsert
@ref as numeric
as
declare curr_insert cursor
for
Select EffDate from amit where MemberReference=@ref
declare @date as varchar(50)
declare @str as varchar(100)
declare @temp as numeric
begin
select @temp=1
open curr_insert
fetch next from curr_insert into @date
while @@fetch_status=0
begin
select @str='update TableName set MemberRowCount='+cast(@temp as varchar)+' where EffDate='+@date+' and MemberReference='+cast(@ref as varchar)
exec(@str)
select @temp=@temp+1
fetch next from curr_insert into @date
end
close curr_insert
deallocate curr_insert
end

the First one passes the ref No to the second one..and seconf procedure updates the memberrowcount column on all the rows with the same ref no and different effdate.

hope this helps.



Amit Jhawar
Developer

Amit,

Many thanks for this.

I see what you have done and have tested it on the small membership I have at the moment and this all looks fine. I will be getting a test cut of about 50000 records tomorrow so I can test it on those too, and refine it if need be, but it looks good.

Once again many thanks

That looks good. Should work as expected.

But you got to be carefull while using cursors, as you say it is got to do with 103Million records. This should be having performance hit on your sql server. Keep a watch.

_________________________
- Vijay G
Strive for Perfection