5. Data Loading : Initial Data Load : COPY Statement
 
Share this page                  
COPY Statement
The COPY statement is a flexible and efficient way to bulk load data from a data file into Vector. COPY can be run locally on the database server, or remotely from an SQL terminal where Ingres Net is available. Note that COPY cannot be run from a JDBC-based application because JDBC does not use Ingres Net to connect to the database.
The syntax for the COPY statement may be different than the syntax for other bulk loaders you are used to. Below is an example of using the COPY statement using the same table definition and data file as mentioned in vwload Utility.
Use the following COPY statement in an SQL terminal session:
COPY TABLE region (
        r_regionkey = 'c0|',
        r_name = 'c0|',
        r_comment = 'c0nl'
)
FROM 'region.tbl' \g
In this example, 'c0|' represents a free format character string (versus fixed length) followed by a pipe delimiter, and 'c0nl' a free format character string followed by the newline at the end of the line.
The COPY statement also supports the commonly used comma or semicolon separated files that use double quote enclosures and the backslash as the escape character. For more information on the COPY statement, refer to the Vector SQL Language Guide.
At present COPY for Vector only supports date, time, and timestamp fields formatted using the ANSI standard:
yyyy-mm-dd for date fields unless you use II_DATE_FORMAT to override the default
hh24:mi:ss for time fields
yyyy-mm-dd hh24:mi:ss.ffffff for timestamp fields
If the load fails for whatever reason, for example due to a bad record, then the default behavior is to stop loading and roll back the entire statement. To diagnose data load issues and identify the data load problem you should use the options the COPY statement provides in its WITH clause. Use ON_ERROR = CONTINUE and LOG = 'filename' to continue the load when hitting an issue, logging bad records to the file identified by 'filename'. Include ERROR_COUNT if you want to stop after a certain number of errors.
For example:
COPY TABLE region (
        r_regionkey = 'c0|',
        r_name = 'c0|',
        r_comment = 'c0nl'
)
FROM 'region.tbl'
WITH ON_ERROR = CONTINUE
, LOG = 'region_bad_records.log' \g