Is it possible to write a VB.NET loop that queries an MS SQL database 1000 rows at a time?

I have an MS SQL database that I need to query.  It has around 10 million rows and I need to be able to create a loop that will:
1. open the connection to the database,
2. query first/next 1000 rows,
3. close the database

any sample code is greatly appreciated.

Who is Participating?
Bob LearnedCommented:
You might be able to use one of the overloaded methods for the SqlDataAdapter.Fill method that will allow you to get blocks of data.

1) Run a Select COUNT(*) query to get the row count for the table.

2) Determine block size

3) Determine the For loop index variables

4) Iteratively call the SqlDataAdapter.Fill method to get blocks of data.
DbDataAdapter.Fill Method (Int32, Int32, DataTable)

"Adds or refreshes rows in a DataTable to match those in the data source starting at the specified record and retrieving up to the specified maximum number of records."
Yes, just put top(1000) in your queries

select top 10000 * from tablename
If you're using 2005 or above, you can use the row_number function to grab the incremental bits:
with ordered as
	select *, ROW_NUMBER() over (order by MyColumn) as Ranking
	from MyTable
select *
from ordered
where Ranking between 1 and 1000
--where Ranking between 1001 and 2000

Open in new window

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

RichardRigaAuthor Commented:
Snarf, it looks like you're ordering the entire table before taking 1000.

here's the thing... i can't use any statement that queries all rows at once.  I have to avoid it due to locking issues.

Chap, I need to be able to do this in a vb loop and grab and process 1000 records at a time.  So... grabbing the first 1000... then grabbing 1001 to 2000, etc.   looping Top 1000 won't work for me, i don't think.
You'll just need to keep track of what you've processed. Identity columns would be perfect here
RichardRigaAuthor Commented:
i'm a tad new to this, can you give me some a sample of psuedocode?
If you don't do it in one action, there is no garantee you'l have covered all the records: data changes, database gets reorganised....
RichardRigaAuthor Commented:
jogos, i know.  this is a continuous service running so whatever updates/inserts it misses, it will catch eventually
My previous comment was  a comment on the ranking-sollution, when you try to sort it on a non-controled way.  With a unique identification that is possible

Putting chapmendew's things together with important additions
select top (1000) from ...
where id > @lastmaxid  --- here provide the highest already treated
order by id  -- you have a unique value AND indexed on this
<this is a continuous service running so whatever updates/inserts it misses, it will catch eventually>
So your code delete's the records or updates them somehowe that you can filter them out to prevent them for being read multiple times?
The top(1000) pefectly works when you can identify the processed records in a WHERE.
- see the id-example above
- or something like WHERE processedFlag = 0


Dim ctr as long = 10000

"SELECT Top 10000 * FROM TableName Where ID Not In (SELECT Top " & ctr & " * FROM TableName orderby ID) orderby ID"  

Note you must change the value of ctr every time you want to increment the retrieve of query.


To retrive the records 20000 to 30000

Just change the value of ctr to 20000.



Jehova is Great!!!

Check out this post

From the post above you have three options:
Option 1:
select * from (
select x.*, RowNum row_num
from (select *, row_number() over (order by FirstName, LastName) as RowNum from employees) x
where RowNum <= 4000
) as y where row_num >= 3000
Option 2:
select * from (
select x.*, RowNum row_num
from (select *, row_number() over (order by FirstName, LastName) as RowNum from employees) x
) as y where row_num between 3000 and 4000
Option 3:
SELECT TOP 1000 * FROM Employees 
WHERE EmployeeID 
        NOT IN (SELECT TOP 3000 EMployeeID FROM Employees ORDER BY)
ORDER BY FirstName, LastName

Open in new window

RichardRigaAuthor Commented:
Thank you, everyone, for your time.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.