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?
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.
