Import and Export Data with bcp

Transact-SQL commands cannot transfer data in bulk. For this reason, use bcp for any large transfers.

Uses for bcp include:

Importing data that was previously associated with another program, such as the records from another database management system. This is the most common use for bcp.
Before using bcp, create a file of the records you want to import. The general steps are:
1. Put the data to transfer into an operating system file.
2. Run bcp from the operating system command line.
Moving tables between Adaptive Servers or between Adaptive Server and other data sources that can produce an operating-system file.
Copying out data from a view.

Note: You cannot use bcp to copy in data to a view.
Transferring data for use with other programs, for example, with a spreadsheet program. The general steps to transfer data are:
1. Use bcp to move the data from Adaptive Server into an operating-system file from which the other program imports the data.
2. When you finish using your data with the other program, copy it into an operating-system file, then use bcp to copy it into Adaptive Server.

Adaptive Server can accept data in any character or binary format, as long as the data file describes either the length of the fields or the terminators, the characters that separate columns.

The structures in the tables involved in the transfer need not be identical, because when bcp:

Imports from a file, it appends data to an existing database table.
Exports to a file, it overwrites the previous contents of the file.

When the transfer is complete, bcp informs you of the:

Number of rows of data successfully copied
Number of rows (if any) that it could not copy
Total time the copy took
Average amount of time, in milliseconds, that it took to copy one row
Number of rows copied per second.

If bcp runs successfully, you see a return status of 0. The return status generally reflects errors from the operating system level and correspond to the ones listed in the errno.h file in the /usr/include/sys/ directory.