Fetch records from sql server database table in parts

Question

Hi All I want to fetch records from a table in my sql server database in parts. Like in one query I want to see first 1000 records in next query next 1000 records. Likewise.. Is it possible with sql server ? I am using sql server 2008. While googling I found LIMIT clause for mysql but it does not work for sql server. Please help.

Dorababu Meka · Answer

Try this First 1000 records: SELECT TOP 1000 * FROM mytable ORDER BY mycolumn General solution (supports offset) SELECT * FROM ( SELECT * ROW_NUMBER () OVER ( ORDER BY mycolumn ) rn FROM mytable ) q WHERE rn BETWEEN 1001 AND 2000 ORDER BY mycolumn

Vijay Yadav · Answer

If your query got resolved please mark the answer

Sam Hobbs · Answer

If you are going to process all records eventually then you can read all in at once and then just use the first 1000 or whatever and then the next and so forth. It would be easier that way. Since you are doing it the other way I won't say any more.

Dorababu Meka · Answer

Welcome Suhani :) Mark as answer

Suhani Mody · Answer

It worked perfectly. Thanks a lot.. :)

Suhani Mody · Answer

yes vijay will try that .

Suhani Mody · Answer

hi Dorababu what about next 1000 records ? ;)

Dorababu Meka · Answer

Have you tried using Top select top 1000 * from tblname where condition select top 2000 * from tblname where condition minus select top 1000 * from tblname where condition

Vijay Yadav · Answer

Hi Suhani You are right LIMIT clause doesn't work for Sql as it work with MySql as far as i am concerned. I think it can be done by using loop while fecthing the records from the database you can apply your logic if the record reach to the 1000th record you can break the loop and then you can fetch the next 1000 record.