title: How do I insert 800000 records into an MS Access table?
body: <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>