Populate Multiple Database Tables Using Multiple FIles
Suppose that the information for the database previously described was stored in data files outside Actian X, 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.
Load a Table from Multiple Sources
Loading the priceinfo table presents special difficulties. The COPY statement can read only one file at a time, but the data needed to load the table resides in a different table as well as the data file.
The solution to this kind of problem varies with the file and table designs in any particular situation. In general, a good solution is to copy from the file containing most of the data into a temporary table containing as many columns of information as needed to complete the rows of the final table.
To load data from the files into the priceinfo table, do the following:
1. Create a temporary table named pricetemp that contains the orderno column, in addition to all the columns of the priceinfo table:
DECLARE GLOBAL TEMPORARY TABLE SESSION.pricetemp (orderno INTEGER2 NOT NULL,
invno INTEGER2 NOT NULL,
suppno INTEGER2 NOT NULL,
catno INTEGER2 NOT NULL,
price MONEY NOT NULL);
ON COMMIT PRESERVE ROWS WITH NORECOVERY;
Adding the orderno column to the temporary table is that it enables you to join the temporary table to the header table to get the supplier number for each row.
2. Copy the data from file2 into the pricetemp table:
COPY TABLE pricetemp (orderno = CHAR(0), invno =
CHAR(0), catno = CHAR(0), dummy = d0, price =
CHAR(0), dummy = d0) FROM 'file2';
3. Insert into the priceinfo table all rows that result from joining the pricetemp table to the header table.
INSERT INTO priceinfo (invno, suppno, catno,price)
SELECT p.invno, h.suppno, p.catno,
p.price FROM header h, SESSION.pricetemp p
WHERE p.orderno = h.orderno;
4. Destroy the temporary table pricetemp:
DROP SESSION.pricetemp;
Multi-line File Records
Another feature of the COPY statement is that it can read multi-line records from a file into a single row in a table. For instance, suppose that for viewing convenience, the detail file is formatted so that each record requires three lines. That file looks like this:
1, 5173
10179A, No.2 Rainbow Pencils
0.29
1, 5175
73122Z, 1998 Rainbow Calendars
4.90
Load these values into the pricetemp table with the following COPY statement:
COPY TABLE pricetemp (orderno = CHAR(0)COMMA, invno = CHAR(0)
NL, catno = CHAR(0)COMMA, descript = d0NL, price =
CHAR(0)nl) FROM 'file2';
It does not matter that newlines have been substituted for commas as delimiters within each record. The only requirement is that the data fields be uniform in number and order, the same as for single-line records.