8. SQL Statements : COPY : Syntax
 
Share this page                  
Syntax
The COPY statement has the following format:
[EXEC SQL] COPY [TABLE] [schema.]table_name
              ([column_name = format [WITH NULL [(value)]]
              {, column_name = format [WITH NULL [(value)]]}])
              INTO | FROM 'filename[, type]'
              [with_clause]
table_name
Specifies an existing table from which data is read or to which data is written.
column_name
Specifies the column from which data is read or to which data is written.
format
Specifies the format in which a value is stored in the file.
filename
Specifies the file from which data is read or to which data is written.
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.
type
Windows: (Optional) Specifies the file translation type (see page 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 page 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'
The following options are valid for bulk copy operations only. For details about these settings, see Modify (see page 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