Re: Bulk Insert

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Jim (j.m.autry_at_earthlink.net)
Date: 05/04/04 

Date: 4 May 2004 08:14:44 -0700

It takes about 400ms to write 13Kb of data. I am also wrapping the
calls in a transaction. I was curious if the data could be formatted
in memory much like a file format and bulk inserted from that point
rather than actually having to write to a file in order to bulk
insert.

"Dan Guzman" <danguzman@nospam-earthlink.net> wrote in message news:<uG0rbtXMEHA.2488@TK2MSFTNGP10.phx.gbl>...
> How long is 'forever'? I wouldn't expect this to take more than a few
> seconds if the inserts are part of the same transaction and you are using
> ADO.NET rather than ADO.
>
> You could build a single string of insert statements (or proc executes) and
> execute these as a single batch. However, without knowing the details of
> what you are doing, I'm not sure this will help performance.
>
>
> --
> Hope this helps.
>
> Dan Guzman
> SQL Server MVP
>
> "James Autry" <j.m.autry@Austin.rr.com> wrote in message
> news:JMBlc.60764$Dn1.1382@fe2.texas.rr.com...
> > Ok, I have to enter up to 3,000 records (56 bytes each)into a table in one
> > shot. It takes forever doing the standard dataadapter/update. I changed
> to
> > a stored procedure to save each record, but the time saved was negligible.
> > Increased connection packet size, no improvement. The only thing left is
> to
> > a bulk transfer, but as I understand, this is not supported in ADO. How
> > would one do it using ExecuteNonQuery on multiple records for the same
> > table. Is there a better way?
> >
> > Thanks,
> > James
> >
> >

Relevant Pages

  • Re: Deleting and Adding a Record to a Table
    ... >(CardID, GiveBackID, LifetimeGiven) ... SQL Server executes all statements ... That will ensure that the lock that's taken by the delete ... LifetimeGiven before starting the transaction. ...
    (microsoft.public.sqlserver.programming)
  • Re: Correct tags to display date
    ... there is an option to format the date-time using the system local ... How about FORTRAN IV? ... JavaScript implementation. ... And that hopefully means the time of the transaction, ...
    (comp.infosystems.www.authoring.html)
  • Re: Access 97 bogus duplicate records
    ... worth checking out. ... this leaves an uncommitted level of transaction. ... Presumably you are using dbFailOnError with all executes. ... worth adding some logging to the procedures, ...
    (comp.databases.ms-access)
  • Re: CString fails to take assignment
    ... occurs on the first iteration of the FOR loop that executes the ELSE. ... // Make sure the transaction is valid ... // Modify transaction ids for split records ... Since you said the Empty() has no ...
    (microsoft.public.vc.mfc)
  • Bulk Insert croaking on datetime column ... would really appreciate a bit of help
    ... involves the migration of delimited data from our current db to SqlServer ... I've been playing with bcp and Bulk Insert for hours now trying to get ... if the error is being caused by a failure to specify the format of the ...
    (microsoft.public.sqlserver.tools)