Running the bcp Utility

The data file containing the imported data needs to be in row/column format so that the bcp utility can understand how the data is to be inserted. Microsoft® SQL Server™ can accept data in any ASCII or binary format as long as the terminators (characters used to separate columns and rows) can be described. The structure of the data file need not be identical to the structure of the SQL Server table because bcp allows columns to be skipped or reordered during the bulk copy process.

Data bulk copied into SQL Server is appended to any existing contents of a table; data bulk copied from SQL Server to a data file overwrites the previous contents of the data file.

The prerequisites for bulk copying data are:

• The destination must already exist.

  The number of fields in the data file do not have to match the number of columns in the table or be in the same order.

• The data in the data file must be text-only (character) format or a format generated previously by the bcp utility, such as native format.

  Each column in the table must be compatible with the field in the data file being copied. For example, it is not possible to copy an int field to a datetime column using native format bcp.

• Relevant permissions to bulk copy data are required on source and destination tables.

  To bulk copy data from a data file into a table, you must have INSERT and SELECT permission on the table. To bulk copy a table or view to a data file, you must have SELECT permission on the table or view being bulk-copied.

Importing and Exporting Data Example

To bulk copy data from the publishers table in the pubs database to the Publishers.txt data file in ASCII text format, execute from the command prompt:

bcp pubs..publishers out publishers.txt -c -Sservername -Usa -Ppassword

The contents of the Publishers.txt file:

Conversely, to bulk copy data from the Publishers.txt file into the publishers2 table in the pubs database, execute from the command prompt:

bcp pubs..publishers2 in publishers.txt -c -Sservername -Usa -Ppassword

Alternatively, using the BULK INSERT statement from a query tool such as SQL Server Query Analyzer to bulk copy data:

BULK INSERT pubs..publishers2 FROM 'c:\publishers.txt'
WITH (DATAFILETYPE = 'char')

Note The publishers2 table needs to be created first.

See Also