Moving data from relational tables into dimensional tables by using external tables

Use SQL statements to unload data from relational tables into external tables, which are data files that are in table format, and then load the data from the data files into the dimensional tables.

Before you begin

Before beginning, document a strategy for mapping data in the relational database to the dimensional database.

About this task

To unload data from the relational database into external tables and then load the data into the dimensional database:

Procedure

Unload the data from a relational database to external tables.
Repeat the following steps to create as many external tables as are required for the data that you want to move.
1. Use the CREATE EXTERNAL TABLE statement to describe the location of the external table and the format of the data.
  The following sample CREATE EXTERNAL TABLE statement creates an external table called emp_ext, with data stored in a specified fixed format:
```
CREATE EXTERNAL TABLE emp_ext
( name CHAR(18) EXTERNAL CHAR(18),
hiredate DATE EXTERNAL CHAR(10),
address VARCHAR(40) EXTERNAL CHAR(40),
empno INTEGER EXTERNAL CHAR(6) )
USING (
FORMAT 'FIXED',
DATAFILES
("DISK:/work2/mydir/employee.unl")
);
```
2. Use the INSERT...SELECT statement to map the relational database table to the external table.
  The following sample INSERT statement loads the employee database table into the external table called emp_ext:
```
INSERT INTO emp_ext SELECT * FROM employee
```
  The data from the employee database table is stored in a data file called employee.unl.
If necessary, copy or move the data files to the system where the dimensional database is located.
Load the data from the data files to the dimensional database.
Repeat the following steps to load all the data files that you created in the previous steps.
1. Use the CREATE EXTERNAL TABLE statement to describe the location of the data file and the format of the data.
  The following code is a sample CREATE EXTERNAL TABLE statement:
```
CREATE EXTERNAL TABLE emp_ext
( name CHAR(18) EXTERNAL CHAR(18),
hiredate DATE EXTERNAL CHAR(10),
address VARCHAR(40) EXTERNAL CHAR(40),
empno INTEGER EXTERNAL CHAR(6) )
USING (
FORMAT 'FIXED',
DATAFILES
("DISK:/work3/mydir/employee.unl")
);
```
2. Use the INSERT...SELECT statement to map the data from the data file to the table in the dimensional database.
  The following sample INSERT statement loads the employee data file into the employee database table:
```
INSERT INTO employee SELECT * FROM emp_ext
```