question

Jeff Oresik avatar image
Jeff Oresik asked

bcp / Bulk Insert difference

The general differences are: bulk insert can only insert and bcp can import and export, bcp runs outside SQL Server and Bulk Insert runs inside. What are some advantages of running inside or outside of the SQL Server process space?
bcpbulk-insertprocessbulk
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
WilliamD avatar image
WilliamD answered
The first advantage that springs to mind is that in-process would allow SQL Server to do better memory management. However, with bcp you have such little overhead that it still runs blazingly fast. Whoever programmed that tool did a very good job of making it lightweight. It really depends on what you want to do, but bcp is more flexible than bulk insert - not just import and export, but you can "shape" the data that is moving. Bulk insert is pretty basic, it allows for column and row terminators and that is about it really. bcp offers more in the way of format files (can be tricky, but when they work, they work well). If you are picking one for a certain job, I would also point you towards SSIS. It is about the same speed as bcp (i think a little faster even, but negligable), but is even more flexible in terms of setting up, error handling (bcp has none i know of) and deployment.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.