INTO EXTERNAL clause

The INTO EXTERNAL clause unloads query results into an external table, creating a default external table description that you can use when you later reload the files.

This syntax fragment is part of the INTO table clauses.

^{(explicit id intcl009)
intcl009} ^{(explicit id intcl008)
intcl008}

INTO EXTERNAL table clause

INTO

EXTERNAL table USING

(

[ <Table Options> , ]

<DATAFILES Clause> ^[]

[ <Table Options> ]

)

Table Options

{ | ^[] FORMAT ' { DELIMITED | INFORMIX } ' | DELIMITER 'field_delimiter ' | RECORDEND 'record_delimiter ' | ^[] ESCAPE { ON | [OFF] } }


Element	Description	Restrictions	Syntax
field_delimiter	Character to separate fields. Default is pipe ( \| ) character	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.	Quoted String
record_delimiter	Character to separate records		Quoted String
table	Name declared here of a table to receive the query results	Must be unique among names of tables, views, synonyms, and sequence objects that you own in the current database	Identifier

The INTO EXTERNAL clause combines the functionality of the CREATE EXTERNAL TABLE . . . SAMEAS and INSERT INTO . . . SELECT statements.

The INTO EXTERNAL clause overwrites any previously existing rows in the external table. Use the Table Options clause of the INTO EXTERNAL clause to specify the format of the unloaded data in the external table.

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.

You use the following keywords to unload data. If you want to specify additional table options in the external-table description for reloading the table later, see Table options.

DELIMITER: Specifies the character that separates fields in a delimited text file
ESCAPE: Directs the database server to recognize ASCII special characters embedded as separators between fields in ASCII-text-based data files Inserts the default escape character immediately before any instances of the field_delimiter separator that DELIMITER specifies, where that character is a literal value in the data, rather than a separator. Whether you include or omit the ESCAPE keyword, this functionality is enabled by default, or you can specify the ESCAPE ON keywords to make it clearer to human readers of your SQL code that this feature is enabled. To prevent literal field_delimiter separator characters in the data from being escaped, you must specify the ESCAPE OFF keywords.
By default, the escape character that the ESCAPE keyword inserts before literal field_delimiter characters is the backslash ( \ ) character. But if the DEFAULTESCCHAR configuration parameter is set to a single-character value, that character replaces backslash ( \ ) for delimiter characters used as literals when ESCAPE or ESCAPE ON is specified.

Important:
The default setting for ESCAPE is OFF in Informix® releases earlier than version 12.10.
FORMAT: Specifies the format of the data in the data files
RECORDEND: Specifies the character that separates records in a delimited text file

For more information about external tables, see the CREATE EXTERNAL TABLE Statement.

¹ Use this path no more than once

² See DATAFILES Clause