SQL Server Bulk Copy Program (BCP)

The Bulk Copy Program is one of several methods to import data into SQL Server. See SQL Server Data Import Methods for further discussion. BCP is a utility that you can run from a command line.

The bcp utility bulk copies data between an instance of Microsoft SQL Server and a data file in a user-specified format. The bcp utility can be used to import large numbers of new rows into SQL Server tables or to export data out of tables into data files. Except when used with the queryout option, the utility requires no knowledge of Transact-SQL. To import data into a table, you must either use a format file created for that table or understand the structure of the table and the types of data that are valid for its columns.

See Microsoft's description: http://technet.microsoft.com/en-us/library/ms162802(SQL.100).aspx

Simple Example

bcp AdventureWorks.Sales.Currency2 in Currency.dat -T -c

The "-T" specifies that the bcp utility connects to SQL Server with a trusted connection using integrated security. The security credentials of the network user, login_id, and password are not required. If –T is not specified, you need to specify –U and –P to successfully log in.

The "-c" performs the operation using a character data type. This option does not prompt for each field; it uses char as the storage type,

Using Format Files

When you bulk import into a SQL Server table or bulk export data from a table, you can use a format file to store format information for each field in a data file relative to that table. A format file provides a flexible system for writing data files that requires little or no editing to comply with other data formats or to read data files from other software programs.

SQL Server 2005 and later versions support two types of format file: non-XML format and XML format. The non-XML format is the original format that is supported by earlier versions of SQL Server.

Generally, XML and non-XML format files are interchangeable. However, we recommend that you use the XML syntax for new format files because they provide several advantages over non-XML format files.