5. Populating Tables : Advanced Use of the Copy Statement : Populate Multiple Database Tables Using Multiple FIles
 
Share this page                  
Populate Multiple Database Tables Using Multiple FIles
Suppose that the information for the database previously described was stored in data files outside Ingres, and that those files, “file1” and “file2,” have the record formats shown below:
orderno,date,suppno,suppinfo,status
orderno,invno,catno,descript,price,quan
The COPY statement can be used to load the data from these files into a five-table database. Assume that the files are entirely in ASCII character format, with fields of varying length terminated by commas, except for the last field, which is terminated by a newline.
The following COPY statement loads the header table from the first file:
COPY TABLE header
    (orderno = CHAR(0)COMMA,
    date = CHAR(0)COMMA,
    suppno = CHAR(0)COMMA,
    dummy = d0COMMA,
    status = CHAR(0)NL)
    FROM 'file1';
Each column of the header table is copied from a variable-length character field in the file. All columns except the last are delimited by a comma; the last column is delimited by a newline.
Specification of the delimiter, although included in the statement, is not needed because the COPY statement looks for the first comma, tab, or newline as the field delimiter by default.
The notation d0 used in place of char(0) tells the COPY statement to ignore the variable-length field in that position in the file, rather than copying it. COPY ignores the column name (in this case dummy) associated with the field described as d format.