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;