Databases
StackOverflow2013
Postsdbo
POHow do I insert 800000 records into an MS Access table?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow do I insert 800000 records into an MS Access table?
    primarykey
    data
    text
    <p>I need to insert 800000 records into an MS Access table. I am using Delphi 2007 and the <code>TAdoXxxx</code> components. The table contains some integer fields, one float field and one text field with only one character. There is a primary key on one of the integer fields (which is not autoinc) and two indexes on another integer and the float field.</p> <p>Inserting the data using <code>AdoTable.AppendRecord(...)</code> takes > 10 Minutes which is not acceptable since this is done every time the user starts using a new database with the program. I cannot prefill the table because the data comes from another database (which is not accessible through <code>ADO</code>).</p> <p>I managed to get down to around 1 minute by writing the records to a tab separated text file and using a <code>tAdoCommand</code> object to execute</p> <pre><code>insert into table (...) select * from [filename.txt] in "c:\somedir" "Text;HDR=Yes" </code></pre> <p>But I don't like the overhead of this.</p> <p>There must be a better way, I think.</p> <p>EDIT:</p> <p>Some additional information:</p> <ul> <li>MS Access was chosen because it does not need any additional installation on the target machine(s) and the whole database is contained in one file which can be easily copied.</li> <li>This is a single user application.</li> <li>The data will be inserted only once and will not change for the lifetime of the database. Though, the table contains one additional field that is used as a flag to indicate that the corresponding record in another database has been processed by the user.</li> <li>One minute <em>is</em> acceptable (up to 3 minutes would be too) and my solution works, but it seems too complicated to me, so I thought there should be an easier way to do this.</li> <li>Once the data has been inserted, the performance of the table is quite good.</li> <li>When I started planning/implementing the feature of the program working with the Access database the table was not required. It only became necessary later on, when another feature was requested by the customer. (Isn't that always the case?)</li> </ul> <p>EDIT:</p> <p>From all the answers I got so far, it seems that I already got the fastest method for inserting that much data into an Access table. Thanks to everybody, I appreciate your help.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. This table or related slice is empty.
    1. USdummzeuch
    plurals
    1. PL
      singulars
      1. LTLinked
    2. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POHow do I insert 800000 records into an MS Access table?
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POHow do I insert 800000 records into an MS Access table?
      1. USJosephStyons
      1. VTFavorite
    3. VO
      singulars
      1. POHow do I insert 800000 records into an MS Access table?
      1. This table or related slice is empty.
      1. VTUpMod
    1. COHow fast do you need it to be? Seems like you got it as fast as possible now considering it took 10 minutes and now only takes 1
      singulars
      1. POHow do I insert 800000 records into an MS Access table?
      1. USEppz
    2. CO1 minute seems very acceptable for inserting 800000 records.
      singulars
      1. POHow do I insert 800000 records into an MS Access table?
      1. USEd S.
    3. CO1 minute is quite acceptable, but I would like to get away without creating this text file.
      singulars
      1. POHow do I insert 800000 records into an MS Access table?
      1. USdummzeuch
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload