CREATE EXTERNAL TABLE Statement

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

The implementation of the CREATE EXTERNAL TABLE statement is an extension to the ANSI/ISO standard for SQL.

Syntax

Read syntax diagramSkip visual syntax diagram
>>-CREATE EXTERNAL TABLE--+---------------+--table-------------->
                          '-IF NOT EXISTS-'          

                         (1)   
>--| Column Definition |---------------------------------------->

                                                         (3)                               
>--USING(-+-----------------------+-| DATAFILES Clause |-----+-----------------------+-)-><
          |                   (2) |                          |                   (2) |     
          '-| Table Options |-----'                          '-| Table Options |-----'     

Notes:
  1. See Column Definition
  2. See Table options
  3. See DATAFILES Clause
Element Description Restrictions Syntax
table The name of the table to store external data Must be unique among names of tables, views, and synonyms in the current database Identifier

Usage

You use external tables to load and unload data to or from your database. You can also use external tables to query data in text files that are not in Informix® databases.

The first portion of the syntax diagram declares the name of the table and defines its columns.

The portion that follows the USING keyword identifies external files that the database server opens when you use the external table, and specifies additional options for characteristics of the external table.

After executing the CREATE EXTERNAL TABLE statement, you can move data to and from the external source with an INSERT INTO ... SELECT statement. See the section INTO EXTERNAL clause for more information about loading the results of a query into an external table.

The CREATE EXTERNAL TABLE statement is not supported on secondary servers within a high-availability cluster.

If you include the optional IF NOT EXISTS keywords, the database server takes no action (rather than sending an exception to the application) if an external table of the specified name is already registered in the systables system catalog table of the current database, or if the specified name is the identifier of a database table, view, sequence object, or synonym in the current database.


Copyright© 2019 HCL Technologies Limited