Re: Inserts so slow using _Connection.Execute

"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
    ... If I use bcp, don't I have to save the records to a text file first? ... five you a decent Delphi example. ... you point a BCP API function to the array and say DO IT for as ... If you can't fit all of the rows into memory at one time, ...
    (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)