Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Mar 2001
    Northern, NJ, New Jersey, USA
    Thanked 0 Times in 0 Posts
    I have one customer table with 13M records that is wreaking havoc on too many processes... (you may have seen my last error topic)

    How can I easily break the table into 13 smaller tables with 1M records each?

    Can I create a make table query and designate specific rows?

    There is always a way.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Thanked 28 Times in 28 Posts
    I wouldn't use a make-table query because it doesn't preserve all field properties of the original table.

    Instead, copy the table to the clipboard, then paste it and select the option to paste the structure only.
    You will now have an empty table with exactly the same structure as the original one.
    You can then copy/paste the empty table as many times as needed.

    Next, create a select query based on the table with 13 million tables, and add * to the query grid to include all fields. Next, specify suitable criteria to limit the result to approximately 1 million records. For example, if the table has an autonumber ID field, you could set a condition <=1000000 on this field.
    Change the query to an append query (Query | Append Query) and specify the first of the new empty tables as target. Since it has the same structure as the original, this should work without problems.
    Run the query.
    Then change the criteria to select the next batch of records, in the above example <=1000000 would become Between 1000001 And 2000000, and specify the second empty table as target (by selecting Query | Append Query again), then run it.
    Repeat for the rest.

  3. #3
    Super Moderator
    Join Date
    Aug 2001
    Evergreen, CO, USA
    Thanked 58 Times in 58 Posts
    How can I easily break the table into 13 smaller tables with 1M records each?
    Have you considered another alternative such as SQL Server for the table storage? I think you will find that splitting the table into multiple smaller ones will create more problems than it solves - obviously I don't understand the business process going on here, but my experience on other projects suggests it is nearly always a bad idea.
    We routinely work with applications approaching that number of records and do it in SQL Server as it handles large recordsets much easier. And we almost always used linked ODBC tables. If you want to explore that option, post back and we can dig up some pointers for you.

Posting Permissions

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