March 5, 2008 at 4:24 pm
I have a result set where the total record count can go from 0 to 500,000 records.
I have to "chunk" the result set out to store them in Excel 2003 sheet where the max sheet limit is 65536 rows.
This means the first Excel sheet will contain the first 65535 rows( 1 row for headers), the second Excel sheet is to contain the next 65535 rows and so on.
What is the best way for me to split the result set into chunks?
Should I use select top or rownumber?
Given this scenario where my result set has 153,000 rows, must I code select top or rownumber 3 times to end up with 3 Excel sheets?
Thank you much in advance
March 5, 2008 at 5:29 pm
Yes, Coriolan, you can use the SET ROWCOUNT n to limit the rows returned. However, what you really need is some sort of paging similar to MySQL's limit function and you have it.
In SQL 2005 there is the new ROW_NUMBER() system function. This will return a row number with the result similar to an identity column. You can then use this to "page" your results to Excel.
DAB
March 5, 2008 at 7:06 pm
You should use the NTILE functionality which will allow you to "chunk" it into multiple pieces of the same size. It's described in Books Online.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
March 6, 2008 at 9:01 am
Thanks! ntile works nicely.
March 6, 2008 at 9:03 am
You're very welcome!!
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply