DAO AddNew vs. Insert Query execution

[syswizard]

Has anyone benchmarked the difference in speed for adding 1000 records to a table ?
I witnessed a programmer looping over an insert statement and that got me wondering. Seems like that would be a lot of overhead, no ?

 

[pbaldy]

Haven't benchmarked it, though it wouldn't be hard to do, but in my experience the AddNew method is faster (presuming a loop adding a single record each pass).

 

[syswizard]

If one had to grab data from an Excel worksheet, (and it's very fast to put the entire UsedRange into an array), I would think AddNew would do the trick. I'd have to specify which sheet columns are mapped to which table columns (An array of specifiers would do fine).
However, if I save the array to a CSV file, I could link to it, and then import the entire set of data using an Insert query. That has to be the fastest method, especially with high volumes of records.

 

[pbaldy]

Certainly the insert query would be faster for a group of records. Adding Excel to the mix also changes the equation. Rather than the array/csv file, I'd probably link to Excel and grab the records directly.