| Author |
Topic |
|
WJHamel
Aged Yak Warrior
651 Posts |
 Posted - 2012-02-14 : 10:17:12
|
| Sql 2008R2, i have a table with 1.85 million rows that i need to export to excel and am running into insufficient memory errors. Not much of a surprise there. So it's apparent i will need to do this in chunks. I know i can 'select top 10000' to get those records, but how would i select 10001 to 20000, etc, and so on to get these batches downloaded?thanks as always. |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-02-14 : 10:25:34
|
quote:
Originally posted by WJHamel
Sql 2008R2, i have a table with 1.85 million rows that i need to export to excel and am running into insufficient memory errors. Not much of a surprise there. So it's apparent i will need to do this in chunks. I know i can 'select top 10000' to get those records, but how would i select 10001 to 20000, etc, and so on to get these batches downloaded?thanks as always.
dump data onto temporary table with identity column and run a looping logic to select a data range from it likeWHILE @startvalue <=@maxIDBEGIN...Excel export logicWHERE RowID BETWEEN @startvalue and @startvalue + 10000change @strtvalue each time to @startvalue + 10001 inside loopstore MAX(RowID) value to @maxid before loop------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
WJHamel
Aged Yak Warrior
651 Posts |
Posted - 2012-02-14 : 10:30:56
|
| why excel? because when the customer asks for excel data they get excel data. "mine is not to question why, blah, blah (something about do or die, i think).thanks again. |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2012-02-14 : 15:16:49
|
quote:
why excel? because when the customer asks for excel data they get excel data. "mine is not to question why, blah, blah (something about do or die, i think).
Perhaps you don't have to question, but you can point out that Excel can only hold about 1 million rows per sheet.Don't take this the wrong way, but when customers make stupid requests and you fulfill them without question, they make more, even stupider requests. There's nothing wrong with educating a customer on understanding and improving their requests. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2012-02-14 : 15:36:49
|
Unless "Excel" really means PowerPivot?
N 56°04'39.26"E 12°55'05.63" |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
WJHamel
Aged Yak Warrior
651 Posts |
Posted - 2012-02-15 : 14:48:20
|
| New Orleans? You mean the new murder capital of the US? about two and a half hrs away. They're calling it "Murder Gras" this year. You won't get me anywhere near there. There's a much better jazz festival coming up there in April!. I'll risk that if i can conceal my Desert Eagle successfully. |
|
|
|
WJHamel
Aged Yak Warrior
651 Posts |
Posted - 2012-02-15 : 14:50:17
|
| By the way, i do have powerpivot added on to Excel 2010. That doesn't increase it's row capacity, does it? At any rate, it's above my pay grade to challenge anyone's thinking around here. They're gonna get what they're gonna get. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2012-02-16 : 04:30:09
|
Standard Excel can only hold 1048576 rows of data.The PowerPivot add-in for Excel can hold hundreds of millions of rows.Also, the PowerPivot add-in has it's own Import from SQL method.
N 56°04'39.26"E 12°55'05.63" |
|
|
|
|
1.85M records