Experiments / Project 702 / Project Challenges

SQL Server bcp Utility to bulk insert data from file

Currently, the climate chip package has two major bottlenecks: the calculation of WBGT and the database write. Both of these processes take 4 – 5 min per year of data. At the moment the database write is a simple iteration with individual insert operations. While this is working, there is plenty of room for improvement. In this post I investigate the performance of SQL Server’s bcp Utility, which

[bcp] copies data between an instance of Microsoft SQL Server and a data file in a user-specified format.

http://technet.microsoft.com/en-us/library/ms162802.aspx

bcp is a command line utility which can be called using the following command:

bcp <database.owner.tableName> in <inputFile> -T -S <serverInstance> -c -r\n -t,

I had a number of errors initially. Firstly, the serverInstance must be included. Secondly (and more difficult to find a solution for), bcp was returning an Unexpected EOF encountered in BCP data-file exception. A quick google showed that this was a common error, but many of the solutions didn’t work for me. Finally I came upon this post which related this exception to IDENTITY columns within the table. A quick test on a table without IDENTITY columns confirmed that this was the problem all along. I tried some of the suggestions for by-passing the IDENTITY issue (SET IDENTITY_INSERT tableName ON, using the -E flag in bcp) but none worked. Thankfully, the climate database doesn’t contain any IDENTITY columns, so this shouldn’t be a problem in the live package.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s