Dear PF:
I would say that is going to depend on what you have to do in order to
generate the values to put into the record.
If you already have 1000 rows in the table, or another table, you
could use an append table to append 1000 new rows to the table from
these, perhaps by just making some modest adjustments to them. Then,
making some different adjustments to them, you could add another 1000
rows.
However, it sounds like it is the performance of this operation that
concerns you the most. Keeping just what you have now you could
probably make it go much faster.
The most important thing you can do is to improve the time it takes to
perform each insertion. This is usually heavily influenced by any
indexes you have on the data. If you temporarily remove all the
indexes on the destination table then the insertions will go very
quickly indeed. After all the insertions are complete you can then
recreate the indexes you need. Whether this is advantageous or not
depends on circumstances.
If you insert 100,000 rows to an empty table without indexing, then
index it afterward, you will almost certainly gain a great deal in
performance. If you insert 1000 rows into a table that already has
100,000 rows, then this would be much slower, and probably would not
benefit.
Another consideration is unique indexes. Can you guarantee the values
generated for insertion will not violate uniqueness for any such
indexes? If so, then you will be in very good shape when creating
this. If not, then you may be unable to recreate the index after
performing the insertions. However, bear in mind that it takes a lot
of effort to check for such a violation if it occurs for each row
added as they occur. Checking for this all at once after the
insertions have completed will take advantage of a much more efficient
"mass indexing" algorithm when done as a separate step after all the
insertions have been done.
Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
