如何在MS Access表中插入800000条记录？ [英] How do I insert 800000 records into an MS Access table?

问题描述

我需要在MS Access表中插入800000条记录。我正在使用Delphi 2007和 TAdoXxxx 组件。该表包含一些整数字段，一个浮点字段和一个仅包含一个字符的文本字段。在其中一个整数字段（不是autoinc）上有一个主键，另一个整数和float字段上有两个索引。

使用<$ c插入数据$ c> AdoTable.AppendRecord（...） take> 10分钟，这是不可接受的，因为这是每当用户开始使用新的数据库与程序完成。我不能预填表，因为数据来自另一个数据库（不能通过 ADO 访问）。

I通过将记录写入选项卡分隔的文本文件并使用 tAdoCommand 对象执行

，设法降低约1分钟在c：\somedir文本; HDR =是中选择* from [filename.txt]
c $ c>

但是我不喜欢这个开销。

一个更好的方法，我想。

编辑：

其他一些信息：

• MS Access被选中，因为它不需要目标机器上的任何额外安装，整个数据库都包含在一个可以轻松复制的文件中。


• 这是一个单一的用户应用程序。


• 数据将只插入一次，数据库的生命周期将不会更改。虽然，该表包含一个附加字段，用作标志以指示另一个数据库中的相应记录已被用户处理。


• 一分钟 >可以接受（最多3分钟），我的解决方案可以工作，但对我来说似乎太复杂了，所以我认为应该有一个更简单的方法。


• 一旦数据已插入，表的性能相当好。


• 当我开始规划/实现使用Access数据库的程序的功能时，表不是必需的。只有在客户要求另外一个功能的时候才有必要。 （不是这样吗？）

编辑：

从所有的答案我到目前为止，似乎我已经有最快的方法来插入许多数据到Access表。感谢大家，我感谢您的帮助。

解决方案

您的文本解决方案似乎是最快的，但如果您可以得到预先分配的MS Access的大小接近尾声。您可以通过填充典型的用户数据库，关闭应用程序（因此缓冲区被刷新）并手动删除该大表的所有记录，但不能缩小/压缩它。

所以，使用该文件开始真正的填充 - Access不会请求任何（或很少）额外的磁盘空间。如果MS Access有一个自动化方法，不要重新计算，但它可以帮助很多...

I need to insert 800000 records into an MS Access table. I am using Delphi 2007 and the TAdoXxxx 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.

Inserting the data using AdoTable.AppendRecord(...) 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 ADO).

I managed to get down to around 1 minute by writing the records to a tab separated text file and using a tAdoCommand object to execute

insert into table (...) select * from [filename.txt] in "c:\somedir" "Text;HDR=Yes"

But I don't like the overhead of this.

There must be a better way, I think.

EDIT:

Some additional information:

• 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.
• This is a single user application.
• 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.
• One minute is 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.
• Once the data has been inserted, the performance of the table is quite good.
• 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?)

EDIT:

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.

解决方案

Your text based solution seems the fastest, but you can get it quicker if you could get an preallocated MS Access in a size near the end one. You can do that by filling an typical user database, closing the application (so the buffers are flushed) and doing a manual deletion of all records of that big table - but not shrinking/compacting it.

So, use that file to start the real filling - Access will not request any (or very few) additional disk space. Don't remeber if MS Access have a way to automate this, but it can help much...

这篇关于如何在MS Access表中插入800000条记录？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！