Exports the data from a table or view into a delimited text file.

Syntax

---

EXPORT TABLE <ExportTable>
TO <FileName>
IN STORE <StoreName>
[(<ColumnName>[,<ColumnName>])]
[FORMAT DELIMITED|XML]
[ENCODING AUTO|ANSI|UNICODE]
[DELIMITER CHAR <DelimiterChar>]
[QUOTE CHAR <QuoteChar>]
[DATE FORMAT <DateFormat>]
[TIME FORMAT <TimeFormat> [AM LITERAL <AMLiteral> PM LITERAL <PMLiteral>]]
[DECIMAL CHAR <DecimalChar>]
[BOOLEAN TRUE LITERAL <TrueLiteral> FALSE LITERAL <FalseLiteral>]
[INCLUDE HEADERS]
[MAX ROWS <MaxRowCount>]

<ExportTable> = <TableName>|<ViewName>

<DateFormat> =

YYYY or YY = Year digits
MM or M = Month digits
DD or D = Day digits
Any other character = literal

<TimeFormat> =

HH or H = Hours digits
MM or M = Minutes digits
SS or S = Seconds digits
ZZZ or Z = Milliseconds digits
N = AM/PM literal
Any other character = literal

Usage

---

Use this statement to export data from a table or view into a delimited or XML text file in the specified store.

Use the optional FORMAT clause to specify the format of the created text file. The format can be specified as DELIMITED or XML, and defaults to DELIMITED if the FORMAT clause is not specified.

For delimited text files, ElevateDB terminates each row of data in the export file with a carriage return (#13) and line feed (#10) character.

For XML text files, ElevateDB outputs the data in the export file in the following format:

<row>
   <columnname>data</columnname>
   [<columnname>data</columnname>]
</row>

The store must have already been created using the CREATE STORE statement, and must be a local store.

Any existing data in the destination export file is overwritten.

You can specify the columns in the export file by including them in parentheses after the TO clause.

The ENCODING clause is used to determine the character encoding of the export file. If this cause isn't included, then the default encoding is AUTO, meaning that ElevateDB will create the export file using the current character encoding of the engine itself (ANSI/UNICODE). Whenever the character encoding of the export file is UNICODE (explicitly or by using AUTO), ElevateDB will always write out BOM (Byte Order Mark) bytes as the first two bytes of the export file.

Use the DELIMITER CHAR, QUOTE CHAR, DATE FORMAT, TIME FORMAT, DECIMAL CHAR, and BOOLEAN LITERAL clauses to control how ElevateDB writes the data to the export file.

If the DELIMITER CHAR clause is not included, then the default delimiter character is the comma ',' character.

If the QUOTE CHAR clause is not included, then the default quote character for character strings is the double quote '"' character.

If the DATE FORMAT clause is not specified, then the default date format is the ANSI SQL standard date format 'YYYY-MM-DD'.

If the TIME FORMAT clause is not specified, then the default date format is 'HH:MM:SS.ZZZ N'. The AM LITERAL and PM LITERAL clauses are only used if the N format specifier is included in the time format.

If the DECIMAL CHAR clause is not included, then the default decimal separator character for character strings is the period '.' character.

If the BOOLEAN TRUE LITERAL is not included then the default boolean True literal value is 'TRUE'. If the BOOLEAN FALSE LITERAL is not included then the default boolean False literal value is 'FALSE'.

For delimited text files, the INCLUDE HEADERS clause determines whether ElevateDB writes the first line in the export file as a list of column names that have been included in the export.

For XML text files, the INCLUDE HEADERS clause determines whether ElevateDB writes the list of column names that have been included in the export in the following format:

<columns>
   <column>columnname</column>
   [<column>columnname</column>]
<columns>

The MAX ROWS clause can be used to limit the number of rows that are exported. This is useful when you simply want to test an import of the file on another system in order to determine whether the export file is being generated correctly.

Examples

---

-- The following example exports the CustomerNo
-- and TotalOrders columns in the Customer table as
-- comma-delimited data into an export file

EXPORT TABLE Customer
TO "custordtotals.txt"
IN STORE "ExportFiles"
(CustomerNo, TotalOrders)

Required Privileges

---

The current user must be granted the SELECT privilege on the table or view from which the data is being exported, and the CREATE privilege on the store in which the export file is being created. Please see the User Security topic for more information.

SQL 2003 Standard Deviations

---

This statement deviates from the SQL 2003 standard in the following ways: