Copy Statement Syntax

To load data from a table into a file or from a file into a table, use the copy statement.

Each copy statement must specify only one table name. An optional schema name can be specified. The table keyword is optional and can be included for readability.

The table name is followed by a list in parentheses containing none, one, or more column format specifications, up to the total number of columns in the table. The column specifications depend on the type of copy being performed.

For additional information on the copy statement, see the SQL Reference Guide.

Copy Into (Unload Data) and Copy From (Reload Data)

The copy statement is bidirectional; it unloads data from a table and loads data into a table.

The into and from keywords specify the direction of data movement:

• The copy into statement performs the unload operation, copying the data into a file from the database. For example, to copy the horx table into the pers.data file, use the following statement:

  copy table horx (name=char(20), code=integer)
  into 'pers.data';

• The copy from statement performs the reload operation, copying the data from a file into the database. For example, to copy the agent table from the myfile.in file into the database, use the following statement:

  copy table agent (ano=integer, code=integer2)
  from 'myfile.in';

A common use of copy is to unload a table for backup to tape or for transfer to another database. In either case, there is the possibility of reloading the data into a database later.

File Name Specification on the Copy Statement

Only one file can be specified in the copy operation. If the file does not exist when copying to a file, it is created.

Windows: If the file exists, copy overwrites it. When specifying a file name, always enclose it in single quotation marks. Omit the drive and directory portion of the file name if the file is in the current directory. Otherwise, provide a full path name with drive and directory. The following example shows a full path name; this is an example of Binary Copying:

copy emp() from 'D:\users\fred\emp.lis';

UNIX: If the file exists, copy overwrites it. When specifying a file name, always enclose it in single quotation marks. Omit the full path name if the file is in the current directory. Otherwise, provide a full path name. The following example shows a full path name; this is an example of Binary Copying:

copy emp() from '/usr/fred/emp.lis';

Important! The copy statement is not able to expand $HOME or recognize the UNIX variables set in your environment. Do not use these variables to specify a path name for the copy statement. For example, the following copy statements do not work:

copy emp () from '~fred/emp.lis';     /* invalid */
copy emp () from '$HOME/emp.lis';     /* invalid */

VMS: If the file exists, copy creates another version of the file. When specifying a file name, you can optionally give a VMS file type:

into | from 'filename[, type]'

where type is text, binary, or variable.

With-Clause Options of the Copy Statement

A with-clause can be used to further describe and control the copy being performed. For a description of the syntax for the with-clause, see the SQL Reference Guide. For valid with-clause options, see the Command Reference Guide.