SQL Reference Guide > SQL Reference Guide > SQL Statements > COPY > COPY Examples
Was this helpful?
COPY Examples
The following examples illustrate the correct use of the COPY statement:
1. In the following Data File Format example, the contents of the file, emp.txt, are copied into the employee table. To omit the city column, a dummy column is employed. The format of the employee table is as follows:
ename     CHAR(15)
age       INTEGER4
dept      CHAR(10)
comment   VARCHAR(20)
The emp.txt file contains the following data:
Jones,J. 32 Anytown,USA toy,00017 This is a comment
Smith,P. 41 New York,NY admin,00015 Another comment
The following diagram illustrates the COPY statement that copies the file, emp.txt, into the employee table, and maps the fields in the file to the portions of the statement that specify how the field is to be copied. Note the following points:
A dummy column is used to skip the city and state field in the data file, because there is no matching column in the employee table.
The department field is delimited by a comma.
The comment field is a variable-length varchar field, preceded by a five-character length specifier.
2. Load the employee table from a data file. The data file contains binary data (rather than character data that can be changed using a text editor).
COPY TABLE employee (eno=INTEGER2, ename=CHAR(10),
    age=INTEGER2, job=INTEGER2, sal=FLOAT4,
    dept=INTEGER2, xxx=D1)
    FROM 'myfile.in';
3. Copy data from the employee table into a file. The example copies employee names, employee numbers, and salaries into a file, inserting commas and newline characters so that the file can be printed or edited. All items are stored as character data. The sal column is converted from its table format (money) to ASCII characters in the data file.
COPY TABLE employee (ename=CHAR(0)comma,
    eno=CHAR(0)comma, sal= CHAR(0)nl)
    INTO 'mfile.out';
Joe Smith , 101, $25000.00

Shirley Scott , 102, $30000.00
4. The same example as #3, except that text format is used to eliminate all padding. The comma-separated-values delimiter is used.
COPY TABLE employee (ename = TEXT(0)csv,
    eno = TEXT(0)csv, sal = TEXT(0)csv)
    INTO ‘mfile.out’;
 
Joe Smith,101,$25000.00
Shirley Scott,102,$30000.00
5. Bulk copy the employee table into a file. The resulting data file contains binary data.
COPY TABLE employee () INTO 'ourfile.dat';
6. Bulk load the file created in the preceding example into another table. The other_employee_table must have the same column definitions as the employee table.
COPY TABLE other_employee_table () FROM 'ourfile.dat';
7. Copy the acct_recv table into a file. The following statement skips the address column, uses the percent sign (%) as a field delimiter, uses 'xx' to indicate null debit and credit fields, and inserts a newline at the end of each record.
COPY TABLE acct_recv
    (acct_name=CHAR(0)'%',
    address='D0%',
    credit=CHAR(0)'%' WITH NULL('xx'),
    debit=CHAR(0)'%' WITH NULL('xx'),
    acct_mngr=CHAR(15),
    xx=D0nl)
    INTO 'qtr_result';
Smith Corp%% $12345.00% $-67890.00%Jones
ABC Oil %% $54321.00% $-98765.00%Green
Spring Omc%%xx %xx %Namroc
8. Copy a table called, gifts, to a file for archiving. This table contains a record of all non-monetary gifts received by a charity foundation. The columns in the table contain the name of the item, when it was received, and who sent it. Because givers are often anonymous, the column representing the sender is nullable.
COPY TABLE gifts
    (item_name=CHAR(0)tab,
    date_recd=CHAR(0)tab,
    sender=CHAR(20)nl WITH NULL('anonymous'))
    INTO 'giftdata';
toaster 04-mar-1993 Nicholas
sled 10-oct-1993 anonymous
rocket 01-dec-1993 Francisco
9. Create a table and load it using bulk copy, specifying structural options.
CREATE TABLE mytable (name CHAR 25, ...);

MODIFY mytable TO HASH;

COPY MYTABLE() FROM 'myfile' WITH MINPAGES = 16384,
MAXPAGES = 16384, ALLOCATION = 16384;
Last modified date: 08/28/2024