Re: Can I append an ADO recordset to an Access table in one go?



Mark J. McGinty wrote:
This is not a trival undertaking. It is neither supported nor
documented. The Microsoft-annointed flock will tell you, "not a best
practice" (apparently, the practice of making things more functional
than they are out of the box is somehow other than "best".)

Large numbers of rows in batch updates tend to perform poorly, you're
better off to chunk them up, no more than a few hundred rows per
batch.
Constraint/integrity violations caused by any rows inserted are
supposed to fail the whole batch, but it doesn't always happen like
that.
SQL Profiler shows that under the hood, ADO is builds a parameterized
INSERT statement for each row. Since you could do that yourself, and
skip the overhead of manipulating the XML, it's worth consideration.

I believe you have touched on the real reasons why I would label this a
less-than-optimal practice rather than the putdown in the initial paragraph.

As I've said time-and-time again, SQL Server has very good builtin
functionality to import data from Excel. There is rarely a reason to resort
to using Ado to perform that task.
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"


.