Wrox Programmer Forums
|
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old July 7th, 2004, 10:58 PM
Authorized User
 
Join Date: Jul 2004
Posts: 13
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to jhawar
Default

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
 
Old July 8th, 2004, 04:27 AM
Authorized User
 
Join Date: Jun 2004
Posts: 18
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

 
Old July 8th, 2004, 01:58 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

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





Similar Threads
Thread Thread Starter Forum Replies Last Post
Counting Child Records BrianWren VB Databases Basics 1 June 2nd, 2008 04:32 PM
Counting 'groups' of records in a report Odeh Naber Access 4 July 30th, 2007 10:41 AM
AbsolutePosition counting filtered records Brad_S VB Databases Basics 1 August 17th, 2006 04:24 AM
Counting records Jonas Access 4 August 4th, 2006 09:07 AM
Grouping and Counting records Con Access VBA 1 September 17th, 2003 11:31 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.