cdfllc
asked on
Select Range of data
Hi, I am looking for a way to select the first 65,000 records from a table, then in another query the next 65,000 records, and so on.
Why am I doing this? Our users need to be able to view this data in Excel - which has a limit of the number of rows 65,653 or something...
So I am wondering if I can setup a DTS command line thing where I can export the first 65,000 into the first excel worksheet, the next 65,000 in the next worksheet and so on...
has anyone come up with a way to do this?
thanks,
ccfllc
Why am I doing this? Our users need to be able to view this data in Excel - which has a limit of the number of rows 65,653 or something...
So I am wondering if I can setup a DTS command line thing where I can export the first 65,000 into the first excel worksheet, the next 65,000 in the next worksheet and so on...
has anyone come up with a way to do this?
thanks,
ccfllc
cdfllc,
Another approach:
1) Use a query like this to create a temp table:
SELECT IDENTITY(int, 1, 1), <other columns>
INTO #temp
FROM <tables>
ORDER BY <something>
2) Use a loop to export the results in chunks of 65535 rows
Regards,
Patrick
Another approach:
1) Use a query like this to create a temp table:
SELECT IDENTITY(int, 1, 1), <other columns>
INTO #temp
FROM <tables>
ORDER BY <something>
2) Use a loop to export the results in chunks of 65535 rows
Regards,
Patrick
ASKER
Yeah, how do I use a loop to do that kind of work? Would that be in a stored procedure?
There are a couple of different ways to do it.
However, I would urge you to think of alternatives. Breaking this across multiple worksheets is clumsy
at best. Why not por the data to an Access database, which can handle millions of rows quite easily?
Or port it to a text file, which again is easily accomplished.
A last consideration is to have your users download the 60-day trial version of Office 2007, which will
expand the allowable rows to 1,048,576.
Patrick
However, I would urge you to think of alternatives. Breaking this across multiple worksheets is clumsy
at best. Why not por the data to an Access database, which can handle millions of rows quite easily?
Or port it to a text file, which again is easily accomplished.
A last consideration is to have your users download the 60-day trial version of Office 2007, which will
expand the allowable rows to 1,048,576.
Patrick
ASKER
I agree that Excel wasn't designed to handle this amount of data, probably. However, the users need to be able to "slice and dice" the data and perform mathematical formulas and stuff on this data for "what if" scenarios, etc.
I don't think they can do that kind of stuff in Access ( I may be wrong )
Anyways, I don't know how to use DTS or something, and specify which worksheet to send it to...
I don't think they can do that kind of stuff in Access ( I may be wrong )
Anyways, I don't know how to use DTS or something, and specify which worksheet to send it to...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SELECT TOP 65535 <columns>
FROM <tables>
ORDER BY <something>
gets the first 65535 rows (Excel's limit is 65536 so leave one for the header; expands to ~1 MM in Excel 2007)
Regards,
Patrick