Re: Inserts so slow using _Connection.Execute

Thanks for that, I will definitely look into this. It seems a good way to do
this. I will check out BOL as you say.
Many thanks for the tip

regards
steve

"Arnie" <none> wrote in message news:442c1b85$1@xxxxxxxxxxxxxxxxxxxxxxxxx
"Steve" <hughessatmdnationwide.co.uk> wrote in message
news:442b90ac@xxxxxxxxxxxxxxxxxxxxxxxxx
Thanks for the input.
If I use bcp, don't I have to save the records to a text file first?

No. SQL Server has a BCP 'C' API. I use BCB and VS2005 so I can't really
five you a decent Delphi example. But, I'm sure Delphi can use C
functions from a DLL.

In general, as I said (more or less) previously, you have to describe your
column layout as a struct (record). You then read and modify your source
records as appropriate and add them to an array (std::vector in C++).
When done, you point a BCP API function to the array and say DO IT for as
many rows as you've accumulated. The entire n row insert is done at once.
If you can't fit all of the rows into memory at one time, the inserts can
be done in 'chunks' of 10,000, 20,000 rows or as many as you can
accomodate in memory. It is extremely fast though certainly more trouble
to code than doing one INSERT at a time. We typically insert hundreds of
thousands of rows in seconds.

As I said before, check out Books Online.

- Arnie



.

Relevant Pages

  • Re: Inserts so slow using _Connection.Execute
    ... No. SQL Server has a BCP 'C' API. ... I use BCB and VS2005 so I ... can't really five you a decent Delphi example. ... can accomodate in memory. ...
    (borland.public.delphi.database.ado)
  • Re: Splitting a 31GB text file into smaller files
    ... In order to isolate the issue I have tried splitting it into smaller files. ... I get an out of memory on the 7th chunk of 10,000,000 lines using the code below. ... Use the bcp utility to load tha data, not bulk insert. ...
    (microsoft.public.vb.general.discussion)