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"


.

Relevant Pages

  • Re: Can I append an ADO recordset to an Access table in one go?
    ... practice" (apparently, the practice of making things more functional ... The reason for my negativity is the tendency to discount/reject very ... Apologies if you objected to the "Microsoft-annointed flock" reference, ... one aside about using batch updates to insert rows: given that ADO ...
    (microsoft.public.data.ado)
  • RE: newbie - lists vs. database
    ... My question is more related to best practice than what is possible. ... to create a database or to let SP handle this for me by ... what is best practice for creating data entry screens when writing a ... Should I just use the edit in dataheet functionality or ...
    (microsoft.public.sharepoint.portalserver.development)
  • Re: Modules to C?
    ... functionality, with the advantage of being existing practice and being ... if they make separate compilation of module headers as easy. ...
    (comp.std.c)
  • Re: Modules
    ... Detect and redirect whenever possible. ... That particular approach is a very good practice, ... Worth googling for! ... I too split on functionality like most of the rest here, ...
    (comp.lang.basic.visual.misc)
  • Re: Modules
    ... Detect and redirect whenever possible. ... That particular approach is a very good practice, ... Worth googling for! ... I too split on functionality like most of the rest here, ...
    (comp.lang.basic.visual.misc)