|
|
|
The FairCom DB SQL import utility, ctsqlimp, has been updated to take advantage of the ability to import multiple files with the same physical name into an SQL database. Simply use the ‑n (symbolic) name option on import to uniquely identify each table in FairCom DB SQL:
>ctsqlimp ./data/A/myfile.dat -u ADMIN -a ADMIN -n myfileA
>ctsqlimp ./data/B/myfile.dat -u ADMIN -a ADMIN -n myfileB
SQL Tip
To create a consolidated view over these two files, create individual views to present the tables as one. A "mapping" table can be created to provide table source identifying information to the view. Consider that myfile.dat contained the fields name, id, and moddate:
CREATE TABLE map (consolidated_name CHAR(32), symbolic_name CHAR(32);
INSERT INTO map VALUES ('myfile', 'myfileA');
INSERT INTO map VALUES ('myfile', 'myfileB');
CREATE VIEW myfileA_vmap AS (
SELECT myfileA.name, myfileA.id, myfileA.moddate, map.consolidated_name, map.symbolic_name
FROM myfileA CROSS JOIN map
WHERE map.symbolic_name = 'myfileA'
);
CREATE VIEW myfileB_vmap AS (
SELECT myfileB.name, myfileB.id, myfileB.moddate, map.consolidated_name, map.symbolic_name
FROM myfileB CROSS JOIN map
WHERE map.symbolic_name = 'myfileB'
);
CREATE VIEW myfile AS (
SELECT * from myfileA_vmap
UNION
SELECT * from myfileB_vmap
);
SELECT * from myfile;
