bcp versus insert bulk with MSSQL

There are 2 ways of importing external data into Microsoft SQL Server:

  1. bcp or bulk copy program, a command line utility.
  2. insert bulk, a T-SQL command.
  • In the old days, the recommendation was to use bcp because it was faster, mostly due to the fact that bcp only did minimal logging.
  • Today, SQL Server 2005/SQL Server 2008, insert bulk does the same logging as bcp.
  • See: How to import web server logs with MSSQL on how to use the insert bulk command.

Applies to:

  • Microsoft SQL Server 2000
  • Microsoft SQL Server 2005
  • Microsoft SQL Server 2008

Performance

  • Since SQL Server 2005, insert bulk is definitely faster than bcp, but we can't prove it with actual I/Os. But bcp copies the data into it's own memory, then copies it to the MSSQL memory [ie: twice the memory I/Os], while insert bulk only does it once.
  • We can see the I/Os cost from insert bulk by looking at the plans and the cost.
  • We can NOT see the I/Os cost from bcp by looking at the plans and the cost. No plan is generated.
  • My experience has confirmed this.
  • Both insert bulk and bcp can fire triggers with the FIRE_TRIGGERS option.
  • If you use: FIRE_TRIGGERS the operation will be fully logged for both the insert bulk and the bcp.
  • Both insert bulk and the bcp will minimally log the operation, with a commit done on the batch size.
  • bcp is a much more versatile program, that allows copying of data between databases, between SQL Servers, import data and export data from other servers and to/from TXT, CVS files.
  • insert bulk only allows you to add new data to a table. The data can either be from another table or from a TXT or CVS file.