Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2009
    Posts
    2

    Unanswered: Append many identical records at once

    I have really hit a wall here, so I’ve finally given up figuring this out myself and decided to ask here.

    This is for work. Basically I need to insert (append) a large number of identical records in a table. At the moment this is (roughly) how I do it using VBA/SQL:


    (counter and total are integers, wordtoinsert is the word I need inserted, which I get from a separate function)

    Code:
    Do while counter < total
    Docmd.runsql “insert into mytable (field1) select “ & wordtoinsert
    Counter = counter + 1
    Loop
    (not the actual code which I don’t have available right now so don’t worry about correcting any typing mistakes)


    Now, this works fine as such, however, as “total” can be quite large (anywhere from 10 to around 10,000 really) it is rather slow, especially since I process hundreds of records at a time.

    Is there a faster way to do this? I’ve been trying to find a way to insert many records at once rather than inserting one record many times but so far I’ve come up blanks. If anybody has any ideas I’d be reeeeally grateful.

    Cheers, Lakejen

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Create a numbers table - I am sure there is code on the forum, or you can probably find some on the internet.
    Then this SQL:
    Code:
    INSERT INTO myTable (col1, col2, col3)
    SELECT "this" AS col1, "that" AS col2, "the other" AS col3
    FROM myNumbersTable
    WHERE number BETWEEN 1 AND 10000
    Inserting multiple identical rows would normally be considered an odd thing to do. You're still happy with it though?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    I shudder to think why you would want such a load of useless data.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  4. #4
    Join Date
    Mar 2009
    Posts
    2

    Thumbs up Thanks

    Obviously it's not useless data - otherwise I wouldn't be doing it

    If you must know, we work with languages and consequently work with some absurdly large corpora and wordlists in over 40 different languages, which we can currently only access and modify via Access. Occasionally we need to increase the frequency of various words (for reasons I won't get into here) - as in literally adding x number of the words in question.

    So far we've been doing it manually

    Anyways, thanks a lot pootle flump, that works a treat. Should speed things up a lot.

Posting Permissions

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