4. SQL Statements : COPY : COPY Examples
 
Share this page                  
COPY Examples
1. Load the lineitem.tbl text file into the lineitem table:
COPY TABLE lineitem (
        l_orderkey = 'c0|',
        l_partkey = 'c0|',
        l_suppkey = 'c0|',
        l_linenumber = 'c0|',
        l_quantity = 'c0|',
        l_extendedprice = 'c0|',
        l_discount = 'c0|',
        l_tax = 'c0|',
        l_returnflag = 'c0|',
        l_linestatus = 'c0|',
        l_shipdate = 'c0|',
        l_commitdate = 'c0|',
        l_receiptdate = 'c0|',
        l_shipinstruct = 'c0|',
        l_shipmode = 'c0|',
        l_comment = 'c0nl'
) FROM 'lineitem.tbl';
2. Export data:
COPY product_master(
     id_pm = c0tab WITH NULL(']^NULL^['),
     name_pm = varchar(0)tab WITH NULL(']^NULL^['),
     intro_pm = c0nl WITH NULL(']^NULL^['),
     nl= d0nl)
     INTO 'product_master.data'
3. Import data and continue when an error occurs. Write invalid records to the log file.
COPY product_master(
     id_pm= c0tab WITH NULL(']^NULL^['),
     name_pm= varchar(0)tab WITH NULL(']^NULL^['),
     intro_pm= c0nl WITH NULL(']^NULL^['),
     nl= d0nl)
FROM 'product_master.data'
WITH ON_ERROR = CONTINUE, LOG = 'product_master.invalid'
 
Executing . . .
E_CO0039 COPY: Error processing row 1. Cannot convert column 'intro_pm' to tuple format.
E_US10D0 13 is not a valid month for a date/time column.
E_CO0039 COPY: Error processing row 3. Cannot convert column 'id_pm' to tuple format.
I_CO002C COPY: 2 rows were written to the Copy Log File 'product_master.invalid'.
E_CO0028 COPY: Warning: Copy completed with 2 warnings. 7 rows successfully copied.
 
(7 rows)
4. Import data but stop after the first error. Write the allowed error record to the log file.
COPY product_master(
     id_pm= c0tab WITH NULL(']^NULL^['),
     name_pm= varchar(0)tab WITH NULL(']^NULL^['),
     intro_pm= c0nl WITH NULL(']^NULL^['),
     nl= d0nl)
FROM 'product_master.data'
WITH LOG='product_master.one_error', ERROR_COUNT = 1
 
Executing . . .
E_CO0039 COPY: Error processing row 1. Cannot convert column 'intro_pm' to tuple format.
E_US10D0 13 is not a valid month for a date/time column.
I_CO002C COPY: 1 rows were written to the Copy Log File 'product_master.one_error'.
E_CO002A COPY: Copy has been aborted.
5. Copy the contents of table t1 into multiple files of 20 MB, and then load the data.
COPY t1() INTO 't1.out' WITH -filesize=20
COPY t1() FROM 't1.out' WITH -filesize