Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2004
    Posts
    3

    Question Unanswered: DTS Export to Excel Too many Recs?

    Hi All,

    I'm trying to export around 115,000 rows from ms sql 2000 into Excel 2000, using the manual process. I just need this one time dump.

    I am able to successfully export around +- 65,000 rows, but the operation fails after that.

    I need to be able to get all the rows out, so using TOP obviously doesn't work.

    Is there some "version" or modified way to use TOP to get...say, rows 65,000 to 90,000 then 90,001 to 115,000 ?

    This would'nt be an issue if the db I'm working with was MySQL...I'd just use the LIMIT function and pull out 3 different chucks. Is there anything similar to LIMIT...or something converse to TOP in MS SQL? Or perhaps another way to dump the table then export it all (or in portions) into Excel?

    Thanks!
    Last edited by rxsid; 01-15-04 at 14:47.

  2. #2
    Join Date
    Feb 2002
    Posts
    2,232
    You need to have a key field(s) that you can sort on. So lets say you have a unique id like excel_id numbered from 1 to 120000:

    select top 65000 ... from table order by excel_id
    select top 55000 ... from table order by excel_id desc

  3. #3
    Join Date
    Feb 2002
    Posts
    2,232
    If you want an inner subset like 65000-90000 then you would use something like:

    select top 25000 ... from (select top 55000 from table order by field desc) order by field

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    If you do have a key then you can use KEY BETWEEN 65001 and 90000, etc. ...and if you were using MySQL we wouldn't have even bothered to look at the question...In short, - DON'T USE IT!

  5. #5
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    Are you using a standard DTS data pump to export the data? Or are you using something else?

    If you are using a data pump then why not take care of the problem programaticly? Select all the records and keep an internal counter so you know when you reach 65000 and when you do change your target to a new spreadsheet zero the counter and continue on....

    Just a thought,....

  6. #6
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    There are plenty of other ways to do this as well, it just depends on the requirements for your export....

  7. #7
    Join Date
    Feb 2002
    Posts
    2,232
    With a programmatic solution, your performance will suffer. Allow the database to do its job.

  8. #8
    Join Date
    Jan 2004
    Posts
    3

    Thumbs up

    Thanks for all the replies/suggestions!

    There is no key field.
    I couldn't find a solution, so I just dumped the data, opened it in textpad, cut out three chunks (~35,000 rows) and imported them into 3 diff .xls files.

    Interesting enough for those in the anti MySQL crowd ... chances are, this very forum almost certainly uses MySQL. I say this for two reasons. 1. PHP server side scripting. 2. This is a vBulletin forum.
    Not to mention the growing interest in open source products vs proprietary!

    Cheers!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •