Home

	Expand Search

Utility Guide

Chapter 3 Using bcp to Transfer Data to and from Adaptive Server

Methods for moving data

Methods for moving data

You can use the following methods to move data to and from your Adaptive Server databases:

• bcp as a standalone program from the operating system. This chapter provides instructions for this method.

• Client-Library, which calls bulk library routines. For more information about the Client-Library, see the Open Client and Open Server Common Libraries Reference Manual.

Importing and exporting data with bcp

Transact-SQL commands cannot transfer data in bulk. For this reason, you must use bcp for any large transfers. You can use bcp to:

• Import 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, you must 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.

• Move tables between Adaptive Servers or between Adaptive Server and other data sources that can produce an operating-system file.

• Copy out data from a view. See bcp for a description of the syntax for using bcp to copy out from a view.

  You cannot use bcp to copy in data to a view.

• Transfer 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, and 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, the number of rows (if any) that it could not copy, the total time the copy took, the average amount of time, in milliseconds, that it took to copy one row and the number of rows copied per second.