Transferring Data to and from Adaptive Server with bcp

This chapter discusses the bcp bulk copy utility, which provides a convenient, high-speed method for transferring data between a database table or view and an operating system file.

bcp can read or write files in a wide variety of formats. When copying in from a file, bcp inserts data into an existing database table; when copying out to a file, bcp overwrites any previous contents of the file.

Versions earlier than 15.0.3 did not allow you to run fast bcp on tables with nonclustered indexes or triggers. Cluster Edition version 15.0.3 and later removes this restriction.

• Methods for Moving Data
  There are two methods to move data to and from your Adaptive Server databases.
• bcp Modes
  bcp in works in one of three modes.
• bcp Requirements
  Before using bcp, provide it with basic data information and prepare both the data for transfer and the command to access the data.
• Copy Data to Partitions Using bcp
  Use bcp to copy data from a table to an operating system file. Specify a table name and one or more partitions as the source.
• Improve bcp Performance
  There are three ways to improve the performance of bcp.
• Bulk Copying Encrypted Data
  bcp transfers encrypted data in and out of databases in either plain text or cipher text form. By default, bcp copies plain text data, data is automatically:
• bcp Options
  The information in this section clarifies some of the more complex options of the bcp syntax.
• Change the Defaults in Interactive bcp
  If you do not specify native (-n) or character (-c) format, bcp prompts you interactively
• Format Files
  After gathering information about each field in the table, bcp asks if you want to save the information to a format file and prompts for the file name.
• Examples of Copying Out Data Interactively
  By changing the default values of the prompts to bcp, you can prepare data for use with other software.
• Examples of Copying In Data Interactively
  To copy in data successfully to a table from a file, know what the terminators in the file are or what the field lengths are and specify them when you use bcp.
• bcp and Alternate Languages
  Adaptive Server stores data using its default character set, which is configured during installation. If your terminal does not support that default character set, it may send confusing characters to bcp when you respond to prompts either by typing or by using host file scripts.
• Support for Initialization Strings
  The bcp utility supports sending Transact-SQL commands, such as set replication off, to Adaptive Server before data is transferred.
• bcp and Row-Level Access Rules
  If Adaptive Server is enabled for row-level access, and you bulk-copy-out data, bcp copies out only the rows of data to which you have access. To copy out the entire table, first drop the access rules, then bcp out. Reinstate the access rules after you are done, if applicable.
• Copy In and Batch Files
  Batching applies only to bulk copying in; it has no effect when copying out. By default, Adaptive Server copies all the rows in batches of 1000 lines. To specify a different batch size, use the command-line option (-b).
• Copy Out and Text and Image Data
  When you copy out text or image data, Adaptive Server, by default, copies only the first 32K of data in a text or image field.
• Specify a Network Packet Size
  To improve the performance of large bulk copy operations, you may want to use larger network packet sizes than the defaults. The -A size option specifies the network packet size to use for the bcp session that you are beginning.
• Copy In and Error Files
  When you specify the -e error_file option with copy in, bcp stores the rows that it cannot copy in to Adaptive Server in the specified error file.
• Copy Out and Error Files
  During the copy out process, as with copy in, bcp overwrites any file of the same name and does not create an error file if no errors occurred.
• Data Integrity for Defaults, Rules, and Triggers
  To ensure integrity, bcp handles data to copy depending upon its element.
• How bcp Differs from Other Utilities
  The bcp utility, which copies entire tables or portions of a single table, is distinct from the other utilities that move data from one place to another.