Using the SAMEAS Clause

The SAMEAS template clause uses all the column names and data types from the template table in the definition of the new table.

You cannot use the SAMEAS clause for FIXED-format files.

Example

Consider loading a delimited ASCII text file into a table with the following schema: 
TABLE employee (
   name CHAR(18) NOT NULL,
   hiredate DATE DEFAULT TODAY,
   address VARCHAR(40),
   empno INTEGER);
The SQL statements used to load data into the employee table would be as follows: 
CREATE EXTERNAL TABLE emp_ext 
SAMEAS employee
USING (
   DATAFILES ("DISK:/work2/mydir/emp.dat"),
    REJECTFILE "/work2/mydir/emp.rej" 
    );
INSERT INTO employee SELECT * FROM emp_ext;

The external table has the same name, type, and default for each column because the CREATE statement includes the SAMEAS keyword. The default format is delimited, so no format keyword is required.

Check constraints defined on columns in database tables are not inherited by the external table. However, NOT NULL constraints are inherited by the external table.

Delimited files are ASCII by default. The default row delimiter is an end-of-line character unless you use the RECORDEND keyword to specify a different delimiter when you created the external table. (The RECORDEND keyword works for delimited format only.)