Optimizing Bulk Insert

I am trying to find the quickest way to have a bulk insert completed. What I
have found from tests is:
1. I need to have the batch size set to a minimum of 4% of the source file
(otherwise the command just quits with no message).
2. I need to have a clustered index and in the bulk insert command, use the
ORDER clause with the same order as the clustered index.
3. I'm not sure if a format file makes this quicker but I am using on.
Please let me know if my findings are incorrect and if there are any other
things that I could be doing to make this go any faster.
Any help would be appreciated.
EllieCheck out this link:
http://www.mssqlcity.com/Tips/bulk_...ptimization.htm
"Ellie" <nospam@.nospam.net> wrote in message
news:ewfl2ibhGHA.5096@.TK2MSFTNGP02.phx.gbl...
>I am trying to find the quickest way to have a bulk insert completed. What
>I have found from tests is:
> 1. I need to have the batch size set to a minimum of 4% of the source file
> (otherwise the command just quits with no message).
> 2. I need to have a clustered index and in the bulk insert command, use
> the ORDER clause with the same order as the clustered index.
> 3. I'm not sure if a format file makes this quicker but I am using on.
> Please let me know if my findings are incorrect and if there are any other
> things that I could be doing to make this go any faster.
> Any help would be appreciated.
> Ellie
>|||Ellie (nospam@.nospam.net) writes:
> 3. I'm not sure if a format file makes this quicker but I am using on.
I believe the fastest format is native format. But that requires of
course that the data comes from a source that can produce native format.
In essence, another SQL Server.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx