Sunday, August 16, 2015

Differences between BCP and BULK INSERT

It is not uncommon to see importing data from other sources and exporting data into other sources as not all data can be entered row-by-row. Generally, either import or export, it can be considered as an ETL operation and some involve with transformation (T) and some do not. If the requirement is just loading a bulk data into SQL Server without transformation, easiest and fastest way of loading data is either using bcp or BULK INSERT. What is the best? Here is a comparison.

Operation bcp BULK INSERT
Type of utility Command line utility  T-SQL utility
Usage for both import and export  Import only
Required TSQL knowledge No, unless queryout is used for writing a query without mentioning the table name. Required. 
Constraint enforcement Does not check CHECK and FOREIGN KEY constraints unless CHECK_CONSTRAINTS is used with -h hint. UNIQUE, PRIMARY KEY, and NOT NULL constraints are always enforced. Does not check CHECK and FOREIGN KEY constraints unless CHECK_CONSTRAINTS is used. UNIQUE, PRIMARY KEY constraints are always enforced. Inserts blank to NOT NULL character type columns if no value is passed to the column.
Explicit transaction Not possible Possible
Format file Can generate and use Cannot generate but can be used.
Source location Can be referred a location in client ENV Can be refereed only a location within the server.
Performance Fast Fastest
Protocol TCP/IP
Shared Memory
Named Pipes
In memory




No comments: