BCP or BULK INSERT? why?

_{Tuesday, August 7, 2012 1:02 PM}

Hi All,

SQL server 2008 R2.

I have a data loading task, which we will be doing every day. File contains around 2 million records.

What is better option? BCP or BULK INSERT? which is quicker and/or resource efficiant? (and obviously why?)

Are there any resource efficiancy tweeks I can use?

I am typically comparing with Oracle, where there is an option to not generate transaction log (i.e. redo log). Also, is there something like, not rebuiling up the indices while loading data, but build/rebuild indices after data load is complete ? I am interested in loading all data in the file and if there is any error, throw away everything (and start again).

Thanks in advance.

_{Tuesday, August 7, 2012 1:35 PM ✅Answered | 3 votes}

You want to aim for minimally logged inserts.  You also should consider SSIS so you can load in parallel.

Have a look through these great articles which explain more.

Optimizing Bulk Import Performance
http://msdn.microsoft.com/en-us/library/ms190421(v=sql.105).aspx

The Data Loading Performance Guide
http://technet.microsoft.com/en-us/library/dd425070(SQL.100).aspx

We Loaded 1TB in 30 Minutes with SSIS, and So Can You
http://msdn.microsoft.com/en-us/library/dd537533(v=sql.100).aspx

_{Tuesday, August 7, 2012 1:23 PM | 1 vote}

1. bulk copy is an utility program: bcp.exe
2. BULK INSERT is a Transact-SQL statement.

bcp.exe uses BULK INSERT to do its job. It's the same relation that the one between sqlcmd.exe (a tool) and SELECT (a statement).

Check this thread for information: BCP & Bulk Inserts – Underappreciated features of Microsoft SQL Server

_{Tuesday, August 7, 2012 3:07 PM}

Thanks irusul, wBob.

That explaination and the links are great.

_{Tuesday, August 7, 2012 9:40 PM | 2 votes}

bcp.exe uses BULK INSERT to do its job.

BCP and BULK INSERT are actually different implementations. Logically they are equivalent, or at least supposed to be, but there are minor differences.

Which of the two you use, depends more on the situations. If you want to load the file from within SQL Server, because the file is accessible from SQL Server, you BULK INSERT (or OPENROWSET(BULK), which uses the same implementation as BULK INSERT). If the file is elsewhere and you want to load it from a client, you use BCP.

As wBob mentioned there is also SQL Server Integration Services which is more versatile - but also one more thing to learn. Personally, I have not come that far yet.

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se