Home
SQL programmingYou can use the HCL OneDB™ implementation of the SQL language to develop applications for HCL OneDB™ database servers.
Guide to SQL: SyntaxThe HCL OneDB™ Guide to SQL: Syntax describes the syntax of the statements, data types, expressions, operators, and built-in functions of the HCL OneDB™ dialect of the SQL language.
SQL statementsThis chapter describes the syntax and semantics of SQL statements that are recognized by HCL OneDB™.
CREATE EXTERNAL TABLE StatementUse the CREATE EXTERNAL TABLE statement to define an external source that is not part of your database to load and unload data for your database.
External Table Examples The examples in this section illustrate different ways to load and unload data using external tables.
Loading Data from External Tables into HCL OneDB™To load data, you define the external data as an external table and then insert the data into the database.
Loading from a Delimited File to a Database Table with the Same SchemaYou can avoid defining the schema of an external table if it has the same schema as the database table.

Loading from a Delimited File to a Database Table with the Same Schema

You can avoid defining the schema of an external table if it has the same schema as the database table.

About this task

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.

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.)