I'm starting a new effort to move a import a huge CSV file. This begs the
question: Which Bulk Import (bcp.exe, bcp task of DTS, BULK INSERT TSQL) is
the best all around, when both speed and maintenance duties are considered?

The medical vendor who provides the data and automated import facility uses
bcp.exe and I suspect format files are used. I'm likely going to press for
using one of the other two approaches. DTS seems the easiest, but are the
operations done in the SQL Server's memory space? Is DTS just a tad slower,
so that ease of maintenance can justify its use?

Any comments appreciated.

Regards,

Michael

Bulk insert is fastest, but there are several things you need to do to
obtain maximum performance.
Have a look at some of the related presentations available here:

http://www.sqldev.net/events.htm

e.g. "Max Rows/Sec: How to Optimize Data Loading Performance"

Personally I use DTS because it is so much easier to manage a overall import
process compared to just using T-SQL. DTS has a Bulk Insert task, which
pretty much a wrapper around the T-SQL functionality. A well setup DataPump
with the correct fast load setting is also very fast, but how much faster
the pure bulk insert solution will be over either of the DTS options I don't
know, and this is only something you can answer through testing.

For manageability I would use DTS. Since you have format files, for ease of
development and potential performance gains, I would use the Bulk Insert
task.
Whichever way you go testing is the key to getting the best performance for
these situations, as there are so many variables between environments.

Darren Green
http://www.sqldts.com

"Michael Clifford" <mjclifford (AT) hotmail_nospam (DOT) com> wrote