getting groups of results from a table with 42000 entries!!

[LucyP]

Hi

I have a table - Subscribers - which contains 42000 entries, 30000 of which match a certain criteria. I want to select all these results in groups of 10000 (so that my scripts don't timeout). Obviously I can select TOP 10000, but how do I get the other 2 groups?

I've added an identity column, but if I select with (the criteria I'm using) and WHERE ID BETWEEN 10000 AND 20000, I'll get substantially fewer than 10000 entries as many entries with IDs between those numbers won't match my criteria. What can I do?

 

[tlbroadbent]

What is your client? There are ways to page through result sets in ADO. A more scalable approach is to use SQL Server stored procedures. Check the following articles.

http://www.microsoft.com/sql/using/tips/administration/resultset.asp

http://www.4guysfromrolla.com/webtech/062899-1.shtml

http://www.aspfree.com/asp/paging_sqlserver.asp

http://www.15seconds.com/issue/010308.htm

http://msdn.microsoft.com/library/en-us/dnserv/html/server05222000.asp

Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.