SQL Reference Guide > SQL Reference Guide > SQL Statements > COPY
Was this helpful?
COPY
Valid in: SQL, ESQL, OpenAPI
The COPY statement does any of the following:
• Copies the contents of a table to a data file (COPY INTO)
• Appends the contents of a table to a data file (COPY APPEND)
• Copies the contents of a file to a table (COPY FROM)
For more information on using the COPY statement, see the chapter “Populating Tables” in the Database Administrator Guide.
Note:  In OpenAPI, COPY is supported through API calls.
The COPY statement has the following format:
[EXEC SQL] COPY [TABLE] [schema.]tablename
              ([column_name = format [WITH NULL [(value)]]
              {, column_name = format [WITH NULL [(value)]]}])
              INTO | APPEND | FROM 'filename[, type]'
              [with_clause]
tablename
Specifies an existing table.
column_name
Specifies the column.
format
Specifies the format in which a value is stored in the file, as described in Column Formats for COPY.
filename
Specifies the file for the operation. When using APPEND, you must ensure that the format of the existing content in filename matches the format generated by the COPY APPEND command.
Note:  OpenAPI does not copy data into/from a file. It returns the data to the application. The information needed to process the file is returned in the copy map, but the application must process the file.
notnull_empty
Keeps empty strings. Does not consider the input value NULL if it is empty (that is, contains two consecutive field delimiters). For VARCHAR, the value becomes an empty string; for CHAR the value becomes blanks. This option applies only to CHAR, NCHAR, VARCHAR, and NVARCHAR columns that are NOT NULL.
type
Windows: (Optional) Specifies the file translation type (see Windows File Types for COPY): T or B.
A space after the comma or at the end of the filename causes the name to be taken literally, rather than interpreting a filetype. If a filename ends in a space, Windows removes the trailing spaces so that they do not appear in the actual file name.
VMS: (Optional) Specifies the file type (see VMS File Types for COPY): text, binary, or variable.
with_clause
Consists of the word WITH, followed by a comma-separated list of one or more of the following items:
• ON_ERROR = TERMINATE | CONTINUE
• ERROR_COUNT = n
• ROLLBACK = ENABLED | DISABLED
• LOG = 'filename'
• COMPRESSION
• FILESIZE = size
• CHARSET = characterset
The following options are valid for bulk copy operations only. For details about these settings, see MODIFY. The value specified for any of these options becomes the new setting for the table and overrides any previously made settings (either using the MODIFY statement or during a previous copy operation).
• ALLOCATION = n
• EXTEND = n
• FILLFACTOR=n (ISAM, Hash, and Btree only)
• MINPAGES=n (Hash only)
• MAXPAGES=n (Hash only)
• LEAFFILL=n (Btree only)
• NONLEAFFILL=n (Btree only)
• ROW_ESTIMATE = n
Last modified date: 11/28/2023