INTO EXTERNAL Clause (XPS)

The INTO EXTERNAL clause unloads query results into an external table, creating a default external table description that you can use when you subsequently reload the files. This combines the functionality of the CREATE EXTERNAL TABLE . . . SAMEAS and INSERT INTO . . . SELECT statements.

Table Options

The SELECT statement supports a subset of the CREATE EXTERNAL TABLE syntax for Table Options. Use the Table Options clause of the SELECT INTO EXTERNAL statement to specify the format of the unloaded data in the external table.

Table Options:
 
          .-,----------------------------------.
    (1)   V             .-DELIMITED-.          |
|-----------+-FORMAT--'-+-INFORMIX--+-'------+-+----------------|
            |            .-ASCII--.          |
            +-CODESET--'-+-EBCDIC-+-'--------+
            +-DELIMITER--'field_delimiter '--+
            +-RECORDEND--'record_delimiter '-+
            '-ESCAPE-------------------------'
 

1. Extended Parallel Server only

The following table describes the keywords that apply to unloading data. If you want to specify additional table options in the external-table description for the purpose of reloading the table later, see Table Options.

In the SELECT ... INTO EXTERNAL statement, you can specify all table options that are discussed in the CREATE EXTERNAL TABLE statement except the fixed-format option.

You can use the INTO EXTERNAL clause when the format type of the created data file is either delimited text (if you use the DELIMITED keyword) or text in Informix internal data format (if you use the INFORMIX keyword). You cannot use it for a fixed-format unload.

Keyword

Effect

CODESET

Specifies the type of code set. Options are EBCDIC or ASCII.

DELIMITER

Specifies the character that separates fields in a delimited text file

ESCAPE

Directs the database server to recognize ASCII special characters embedded in ASCII-text-based data files

If you do not specify ESCAPE when you load data, the database server does not check the character fields in text data files for embedded special characters.

If you do not specify ESCAPE when you unload data, the database server does not create embedded hexadecimal characters in text fields.

FORMAT

Specifies the format of the data in the data files

RECORDEND

Specifies the character that separates records in a delimited text file

Specifying Delimiters

If you do not set the RECORDEND environment variable, the default value for record_delimiter is the newline character (CTRL-J).

If you use a non-printing character as a delimiter, encode it as the octal representation of the ASCII character. For example, '\006' can represent CTRL-F.

For more information on external tables, see CREATE EXTERNAL TABLE (XPS).