Six traps for SQL bulk copy commands

The bulk copy tool (BCP) is one of the primary command-line tools for SQL Server, and it is also a convenient way for SQL Server to import large amounts of data. However, DBAs should note that there are several limitations to BCP, the author of this article through their own experience to sum up some of the major problem performance.

1, no support for the UTF-8

SQL Server has local support for Unicode, which is known to anyone who has used the nvarchar and ntext field types. It handles Unicode internally by mapping each character to a two-byte entity. If you're just working with data between instances of SQL Server, there's no problem, because they're all stored in the same way.

However, if you attempt to use BCP to import data from a data source that exports Unicode as a UTF-8, it is a bit complicated. UTF-8 is a variant of Unicode that is designed to support backwards compatibility with eight-bit ASCII text, so the default use of eight-bit ASCII-encoded Web pages, e-mail, and other formats can be used to store Unicode data.

If you export data from a UTF-8 source, do not expect to use BCP for that data; it does not support UTF-8. You have to consider the data problem and make the data in an acceptable format with a full double-byte Unicode export. Ironically, another common encoding can be accepted by bcp via the "-C" switch (ISO 1252,ansi/Microsoft Windows). On the whole, however, you might want to export the data to double-byte Unicode to maintain maximum compatibility with bcp, especially if the data you are working with may contain characters that are incompatible with ASCII.

2, note the export order of the rows

Using bcp to follow the same rules for the export order of data that is exported from a query applies to any other query. In other words, if your query does not have a clear "ORDER BY" clause, the data you obtain appears to be in a completely arbitrary sequence. It is usually based on the order in the implied index, but I have learned not to believe even the rules of thumb-especially if the query performs "JOIN" or some other aggregate function between tables.

The order in which data is exported is not usually important, but the order in which data is imported is critical. If you are using a database that is based on the correctness of the imported rows, and you are importing data in batches, then the order of the export is important and you need to build your bcp statement accordingly. This seems obvious, but I'm often surprised that so many people, even some senior SQL Server experts, are unaware of this.

3. Stored procedures activated from BCP cannot receive parameters

If you use a stored procedure with parameters, as part of a BCP action Transact-SQL (T-SQL) statement, you can almost certainly not use it and throw a function order error on the command line.

When a T-SQL statement is passed to BCP, it is parsed using the "SET fmtonly on" mechanism to determine the columnar format of the result set. This means that dynamic construction statements, such as stored procedures with parameters, will not parse correctly and cannot be compiled under BCP.

If you want to solve this problem, there are several ways to choose:

Create a stored procedure with no parameters, activate the stored procedure with a question mark, and pass in the required parameters (possibly through the data source instead of the command line).

Replace BCP with SQLCMD.

The MSDN blog mentions a processing technique that requires the use of a technique called "OPENROWSET". If you run "SELECT" through the "OPENROWSET" function, you can pass a T-SQL statement in a temporary manner to resolve the restriction of invoking a stored procedure with parameters. However, this processing technique is also limited: for example, it should not be used when connecting to a statement, because running can cause negative changes to the database, and the statement may need to run more than once.

4, the import should pay attention to table definition

When you use BCP to export data from a SQL Server source and import it into another SQL Server, the column definitions you export and the column definitions you import must match. This also includes definitions such as NULL or NOT NULL, which can cause silent data corruption if the target table is missing them.

5. Triggers on the target database cannot be triggered by bcp

Whenever you run the import operation, the local behavior of BCP disables the trigger on the target database. Because the bcp import operation is usually large, the import operation can be confusing if the trigger is enabled by default. Therefore, you need to use the command option "-H fire_triggers" on bcp so that triggers are triggered.

Note that when the selected item is enabled, the trigger runs once for each bulk operation-that is, every time you run bcp. Also note that in SQL Server 2005 and later versions, the triggers use row version, and tempdb is used to store row version information during the import operation. If your tempdb cannot accommodate a large influx of data generated by triggers, the operation will terminate abnormally.

6. BCP cannot attach file output to local

If you use BCP to export data to a file, the file must be newly created. You cannot select an existing file and append the results of the export to the file. Fortunately, the solution is not difficult, you can simply export to any number of files and then use the Copy command to consolidate the results. The order is as follows:

COPY Export1.dat + export2.dat Export.dat