Copy Statement Operation

The copy statement allows you to do the following:

• Copy all of a table or selected columns
• Rearrange columns
• Manipulate data formats of the column data

Binary and Formatted Copying

A special form of the copy statement is used to perform a fast copy of an entire table with no format changes.

• A binary copy is a fast method of copying an entire table.

  No column names are specified in a binary copy. For example:

  table emp ()

  The entire table is moved, byte for byte, with no record delimiters or data type conversions.

• A formatted copy is performed on selected columns of data (which can include all columns), with type conversions being performed as necessary during the copy.

  One or more column names are specified in a formatted copy. For example:

  table emp (eno=integer, ename=char(10))

  Although this type of copy is not as fast as a binary copy, it allows you to fully control the columns copied, the column order, and the data type conversions to be performed.

Bulk and Incremental Copy

When copying data to a table, the copy from statement can run in either of the following modes:

• Bulk copy

  A bulk copy is a copy operation optimized for speed that allows the DBMS Server to exploit group writes and minimal transaction logging. Bulk copying is done whenever the characteristics of the target table allows. The bulk copy can be performed from any source file, either binary or formatted.

  Doing a bulk copy from a binary file is the fastest method to copy large amounts of data.

• Incremental copy

  In incremental mode, data is added to the table using inserts, causing single-page writes and full transaction logging. Incremental mode is used for the copy whenever the characteristics of the target table do not allow a bulk copy to be performed.

Copy Permissions and Integrities

The copy statement itself does not require permissions to run. However, you must have permission to access the table being copied. At least one of the following must apply:

• You own the table.
• You have been granted select (for copy into) or insert (for copy from) privilege on the table.
• The table has select (for copy into) or insert (for copy from) permission granted to public.

To copy data in and out of the database as quickly as possible, the copy statement does not check for:

• Permissions other than select/insert as described above
• Integrities

  When copying data into a table, copy ignores any integrity constraints (defined with the create integrity statement) against the table.

• Constraints

  When copying data into a table, copy ignores ISO Entry SQL92 check and referential constraints (defined using the create table and alter table statements), but does not ignore unique and primary key constraints.

• Rules

  The copy statement does not fire any rules defined against the table.

For information on integrities, constraints, and rules, see the chapter "Ensuring Data Integrity."

Locking During a Copy

When you use the copy statement, the locking system takes one of the following actions:

• A shared lock on the table while data is being copied into a file
• An exclusive lock on the table during bulk copies to the table, for maximum speed
• An Intended Exclusive lock on the table during an incremental copy to the table. Because inserts are used to update the table, the copy can encounter lock contention.

For a complete explanation of locking, see the chapter "Understanding the Locking System."