Fastest Bulk Load

Hi All,

Im bulk loading a ton of data into MSSQL SERVER 2005 Standard Edition. I used to do this process in version 2000. It seems there is some more overhead in 2005. Is there a way to drop logging to almost null to speed up insert?

This is my current sql statment to load data.

EXEC sp_dboption 'my_stuff', 'select into/bulkcopy', 'true'

SET ANSI_WARNINGS OFF

BULK INSERT mystuff.dbo.[v1]

FROM 'c:\myfile.txt'

WITH

(

FIRSTROW = 1,

FORMATFILE = 'c:\scripts\v1.fmt',

MAXERRORS=2000,

ROWS_PER_BATCH=100000

)

Thanks,

Mike

There should not be any significant difference in what gets logged for this between SQL 2000 and SQL 2005. How are you measuring the discrepency?

|||

I used to load this data on a windows 2000 server running sql server 2000. And I could load all of the data into 7-8 hours. Im running 22 scripts that load from 8 files a piece and specfic start and end points in the fmt files. The fmt also jump around through the data. The data files are 25 GB a piece. But now with the New version on a more powerful box the time esimates are going to be days. I guess I need to start looking for other variables if its not the logging.

I should also mention that the data all resides on a NetApp san and we are connected over 4GB fibre.

Thanks,

Mike