Preparing Data for Importing and Exporting

In order for the bcp and BULK INSERT utilities to insert data, the data file must be in row and column format. 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 does not need to be identical to the structure of the SQL Server table because bcp and BULK INSERT allow columns to be skipped or reordered during the bulk copy process.

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

To bulk copy data:

• If importing data, the destination table must already exist. If exporting to a file, bcp will create the file.

  The number of fields in the data file does 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 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 files and tables.

  To bulk copy data from a data file into a table, you must have INSERT and SELECT permissions 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 bulkcopied.

Before using bulk copy operations, consider the following:

• It is possible to specify the number of rows to load from the data file rather than loading the entire file. For example, to load only the first 150 rows from a 10,000 row data file, specify the -L last_row switch when loading the data. This can be useful for testing a batch load process.
  
  
• When using the -F first_row switch to specify the first row in the table or view to bulk copy, all rows in the table or view are first returned to the client, and then the bcp utility determines which rows to skip and write to the data file. Therefore, specifying -F first_row does not limit the amount of data returned to the client and does not necessarily cause the bulk copy operation to execute any faster.
  
  
• Because SQL Server can use parallel scans to retrieve data, the data bulk copied from an instance of SQL Server is not guaranteed to be in any specific order unless you bulk copy from a query and specify an ORDER BY clause.
  
  
• To copy data from earlier versions of SQL Server using native format data files, use the same version of bcp for importing, exporting, and formatting files.

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, from the command prompt, execute:

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, from the command prompt, execute:

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

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

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

Note  The publishers2 table must be created first.

See Also

bcp Utility

BULK INSERT.

Character Format

ExportData Method

ImportData Method

Managing Security Accounts

Native Format