How to Select row range wise in SQL Server

Question

I Have a very large database in SQL Server ( around 20 Million Rows ). I have to take backup of that database in .csv format. But .csv supports only 1 million rows in a single file. So I am not able to take backup of whole database. So I have to break that database in 20 Parts.

For that I have to select 1-1 Million then 1 million to 2 million and so on.

So for this I can take 1st 1 million by using select TOP query.But I am not able to retrieve 1 Million to 2 million and so on....

So Please help me to do so. What tricks I should use or what should I do to take backup in .csv files.

Answer 1

Use the LIMIT clause:

http://dev.mysql.com/doc/refman/5.0/en/select.html

Ie:

/* rows 0-1 million */
SELECT * FROM tbl LIMIT 0,1000000;
/* rows 1-2 million */
SELECT * FROM tbl LIMIT 1000000,1000000;
/* etc (these might be off by 1) */

Answer 2

try the following

select tab.col1,tab.col2,..tab.coln
from (
select a.*.row_number() over(order by Id) as rn 
from table as a) as tab
where tab.rn between limit1 and limit 2
order by id