Results 1 to 7 of 7

Thread: Split a Table

  1. #1
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1

    Question Unanswered: Split a Table

    I hav a a table contains 1700 Records, I want to split it into 3 or for tables, having the same structure per SQL

    something like that,

    SELECT TOP 600 FROM myTable INTO subTable1.

    How could I select from 601 to 1200?
    Thanks

  2. #2
    Join Date
    Jul 2003
    Location
    Canberra, ACT Australia
    Posts
    53
    put the top 600 into table1, delete them from original table, repeat until done increasing tablenumber. The top 600 will be from those remaining in the original table, which will decrease in size by 600 on each loop.

    cheers

  3. #3
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1
    Originally posted by rodmead
    put the top 600 into table1, delete them from original table, repeat until done increasing tablenumber. The top 600 will be from those remaining in the original table, which will decrease in size by 600 on each loop.

    cheers
    Thank you for reply, it functios manually, but I want to do it prgrmatically using vba.

    Therefore it is difficlut to delete the first 600 without a condition.

    Thanks anyway

  4. #4
    Join Date
    Nov 2003
    Location
    Plymouth, UK
    Posts
    59
    why don't you export the data into excell or whatever or a seperate table, set up a table with an auto number generating for each record, hide this field and then in the query split the table using these auto numbers???? Works in theory, I think, but you would have to set up the condition on an empty table and then re-inport the 1700 records in and then write the query.. Let me know if it works,

    Dave

  5. #5
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1
    Originally posted by Davekingwcp
    why don't you export the data into excell or whatever or a seperate table, set up a table with an auto number generating for each record, hide this field and then in the query split the table using these auto numbers???? Works in theory, I think, but you would have to set up the condition on an empty table and then re-inport the 1700 records in and then write the query.. Let me know if it works,

    Dave
    Thank you for your reply, your idea is good, but I got another idea using the SQL here it is
    SELECT TOP 400 *
    INTO temptable1
    FROM maintable

    SELECT TOP 400 *
    INTO temptable2
    FROM maintable
    WHERE id not in
    (select id from temptable1)

    SELECT TOP 400 *
    INTO temptable3
    FROM maintable
    WHERE id NOT IN
    (select id FROM temptable1)
    AND id NOT IN
    (SELECT id FROM temptable2)

  6. #6
    Join Date
    Nov 2003
    Location
    Plymouth, UK
    Posts
    59
    yeah that would work too, ideal, I will keep a copy of that code if you don't mind,

    cheers Dave.

  7. #7
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1
    Originally posted by Davekingwcp
    yeah that would work too, ideal, I will keep a copy of that code if you don't mind,

    cheers Dave.
    Sure you can do, I'll try to program it too.

Posting Permissions

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