Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

AddNew

16 views
Skip to first unread message

Daedalus

unread,
Nov 10, 2009, 5:11:09 PM11/10/09
to
Hi all

I'm importing a txt-file to an SQL-server db via Access (linked files). I
therefore use ado, with of course the addnew method of a recordset opened
with CurrentProject.Connection, adOpenDynamic, adLockOptimistic
I now see the addnew is extremely slow. Output is several millions of rows,
so it's taking hours if not days to run.
Is there any way I can change this to a more performant solution?
Tanx in advance !

D


Salad

unread,
Nov 10, 2009, 7:39:01 PM11/10/09
to
Daedalus wrote:

I'd think an append query. Here's an example.
http://blog.sqlauthority.com/2008/02/06/sql-server-import-csv-file-into-sql-server-using-bulk-insert-load-comma-delimited-file-into-sql-server/

Tom van Stiphout

unread,
Nov 10, 2009, 10:42:34 PM11/10/09
to
On Tue, 10 Nov 2009 23:11:09 +0100, "Daedalus"
<fdpro...@hotmail.com> wrote:

In addition to the Append query already suggested:
SQL Server has its own features for importing data, most notably SSIS
= SQL Server Integration Services. You can kick off an SSIS package
using VBA. Not exactly simple, but doable.
BCP = Bulk Copy Program is another SQL Server tool to look into.

-Tom.
Microsoft Access MVP

The Frog

unread,
Nov 13, 2009, 3:43:12 AM11/13/09
to
Since you are using ADO with SQL Server you could always create a
recordset for the target table, start an ADO transaction, do the row
additions (say 10,000 at a time), then commit the transaction - repeat
until finished. I found that this is extremely quick when parsing text
files, especially when you have to put 'cleaning' processes into the
data handling to make sure that the incoming data is what it is
supposed to be.

Cheers

The Frog

0 new messages