Re: SQL TOP 50,000 Help


--
Dave Hargis, Microsoft Access MVP


"Bob Hairgrove" wrote:

On 9 Feb 2007 08:17:34 -0800, "Brandon Johnson" <xtant.9.11@xxxxxxxxx>
wrote:

Basically what I'm trying to do is take ALL the information from a
130,000 record table and send chunks of 50,000 to its own individual
excel documents. The problem lies when the record count in excel
exceeds the 50,000 mark after i send the information over. I don't
understand whats going on. If ANYONE coudl help that would be
EXCELLENT! Thank you in advance.

Is there a documented limit to the number of rows Excel can handle, or
is this perhaps a limitation of hard disk or temp directory space, or
maybe virtual memory?

Excel versions up through 2003 is 65K rows
Non of the other items you listed should be any problem.
The problem is how the TOP predicate works in SQL


With this many rows, I would try to import everything first to a
tab-delimited text file. You can change the ending of such a text file
from *.txt to *.xsl and Excel will open it without a problem
(assuming, of course, that there aren't other problems such as too
many rows).

An exhaustive waste of time and energy


As someone else pointed out, the "TOP n" predicate in SQL doesn't
always do what you need it to. I would think that it is probably very
important that you don't have any duplicate rows in the resulting
Excel file(s), and with "TOP n" you might.

Does this table have a primary key or unique index? If so, and you
still run into problems when doing the export via a text file, I would
write a little hash function which generates a number, or "bucket",
which can be used to filter the records. If you pass the primary key
value as an argument, it should perform fairly quickly ... 100,000
rows or so isn't really that many. If the primary key field type is
AutoNumber or long integer, you might simply use the MOD function for
this purpose. Besides, you avoid the "ORDER BY" clause doing it that
way -- that can be a potential performance killer (unless you have an
index on that column or group of columns).

For example, say you have 130,000 rows or so and want to split it up
into 5 buckets of approx. 20,000 rows each. Assuming that the PK
values are more or less sequential and/or randomly distributed, you
could do five SELECT statements using "WHERE ([primary key] Mod 5)=n"
as criteria where n is an integer between 0 and 4.


Sorry, Bob, I don't see the point of all this. The OP is trying to return
the TOP 50,000 records based on some criteria which to work requires the
ORDER BY.
--
Bob Hairgrove
NoSpamPlease@xxxxxxxx

.

Relevant Pages

  • Re: SQL TOP 50,000 Help
    ... excel documents. ... The problem lies when the record count in excel ... Does this table have a primary key or unique index? ... write a little hash function which generates a number, or "bucket", ...
    (microsoft.public.access.formscoding)
  • Re: Totally Lost...Please Help
    ... Veronica, import the Excel flat table as is, and go Tools> Analyze> ... >>| access asign the primary key but ofcourse it gave every record a number. ... >>| primary key to the duplicate record or to recognize it is a duplicate record ...
    (microsoft.public.access.formscoding)
  • Re: Combining a data from a linked table and a regular table in one fo
    ... Create a table with the primary key that you need from the Excel worksheet. ... Make an append query that appends the excel data to the table you created ... I have a regular table with 4 more fields (1 of wich has the same name as ...
    (microsoft.public.access.forms)
  • Re: Printing a report
    ... Print Preview mode and will print just the record whose Primary Key value is ... You might explain to your boss that Access has a much steeper learning ... curve than Excel. ...
    (microsoft.public.access.gettingstarted)
  • Re: "Index or primary key cannot contain a Null value" error messa
    ... If you took it out to Excel and bringing it back again you are probably ... and therefore have nulls in the autonumber field. ... As I said bring the data in a table with primary key and see what is being ... my coworker exported a table into Excel to fix data in ...
    (microsoft.public.access.gettingstarted)