Speed Inserts with BCP

Download the source code and sample data (214 kb) for this article, either the Sybase or Microsoft version.

BCP or BULK INSERT is a technology that is used quite extensively by DBAs, but very rarely by developers. And that's a shame because the performance gains on INSERTS compared to calling stored proc for each row to be inserted are tremendous. The gains are on the order of 50:1. Seriously.

With that in mind, we present a handy BCP class. Download the code and take a look at it. The class comes in two varieties: Microsoft SQL Server 7.0 or higher and Sybase Adaptive Server Enterprise 11.x or higher. The interface for both classes is the same, however the implementation is different.

Let me explain a bit how BCP works from user's perspective. It needs 2 things: a data file and a format file. The data file contains all the data that will go in. The rows are normally comma separated. The format file contains just that: the format of data. Both Microsoft SQL Server and Sybase Adaptive Server provide 2 ways to BCP data. The first method is the same between both database: execute a command-line utility called BCP.EXE with about a million parameters. Second method differs. Microsoft SQL Server allows to execute inline SQL to get the data going. Sybase makes you call a set of functions in the OpenClient library (set of DLLs, true DLLs, not ActiveX). Well, my friends, I am not about to learn another API set. Thus, Sybase is implemented using the Command-Line utility, while Microsoft version is done through the use of inline SQL. If anyone knows how to do the same way in Sybase, I am all ears.

Note of caution: many DBAs frown on developers using BCP and turn it off in the database. So if you get errors, check the settings first. Another common source of errors is that the fields in the source data do not match the fields in the target table. SourceRecordset property must be passed a recordset that matches the target table.

Included in the download is the following:

BCP class
Demo program to show it off
Access database with sample data to be inserted.

You will need the following:

Either Microsoft SQL Server 7.0 or higher or Sybase Adaptive Server Enterprise 11.x or higher
Rights to create a table (for the demo only)
Rights to INSERT data

Possible problems:

You may be denied inserts. Make sure that your login has appropriate rights, check that the database allows BCP and/or BULK INSERT.
Updates are too slow. You may be trying to insert into a database that is almost full and it has to grow itself for each insert. Or you maybe inserting into a table that has a clustered index. This means that for each inserted row the database has to resort the table.
You are getting data errors - some rows do not want to insert themselves. If you are trying to stuff a varchar(255) into a datetime field, you are not going to get far. Make sure you know what you inserting and where.
The error message states that the log segment is full. This means that your log file is running out of space. Solution: truncate the existing log or make it bigger. That's just the way databases work.

The class exposes the following properties (required are bolded) & methods:

Type	Name	Data Type/Return Type	Notes
Properties	TargetConnection	ADODB.Connection	Connection to the target database
	SourceRecordset	ADODB.Recordset	Recordset which holds records to be inserted
	TargetTable	String	Data will be inserted into this table
	LastError	String	Reports any errors. Call if the method returns False
	BcpExePath	String	Ignored in the MS version. In Sybase version, use this property to point the class to the BCP executable, if it is not already set in the path
	ColumnSeparator	String	Comma by default. Change it if your data contains commas.
	DataFileName	String	Default name is default.dat. Class creates this BCP compatible data file. There is no reason to change it, unless your application is only allowed to access certain directories.
	ErrorFileName	String	Default name is default.err. Ignored by MS version. Sybase BCP generates this file when an error has occurred. There is no reason to change it, unless your application is only allowed to access certain directories.
	FormatFileName	String	Default name is default.fmt. Class creates this BCP compatible format file to let BCP know how to import data. There is no reason to change it, unless your application is only allowed to access certain directories
	RowSeparator	String	Carriage Return & Line Feed by default (vbCrLf). Change it if your data contains vbCrlf.
	TargetDatabase	String	Required with the Sybase verion. Optional with the Microsoft version. If you want to insert data into a database different from the one you are connected to, then change this property
	TargetDatabaseOwner	String	If you want to specify the name of the owner of the database/table, then change this property
	UserName	String	Required with the Sybase version. Ignored by MS BCP class. Your login ID.
	Password	String	Required with the Sybase version. Ignored by MS BCP class. Your login Password.
Methods	UploadData	Boolean	Call this after setting all properites
Methods	CleanUp	None	Call this after performing BCP