|
Outputs the current query results to a file.
OUTPUT TO filename [ APPEND ] [ BYTE ORDER MARK { ON | OFF } [ COLUMN WIDTHS ( integer, ...) ] [ DELIMITED BY string ] [ ENCODING encoding ] [ ESCAPE CHARACTER character ] [ ESCAPES { ON | OFF } [ FORMAT output-format ] [ HEXADECIMAL { ON | OFF | ASIS } ] [ QUOTE string [ ALL ] ] [ VERBOSE ] [ WITH COLUMN NAMES ]
output-format : TEXT | FIXED | EXCEL | HTML | SQL | XML
encoding : string | identifier
OUTPUT USING connection-string INTO destination-table-name [ CREATE TABLE { ON | OFF } ]
connection-string :
{ DSN = odbc-data-source
| DRIVER = odbc-driver-name [; connection-parameter = value [; ... ] ] }This optional keyword is used to append the results of the query to the end of an existing output file without overwriting the previous contents of the file. If the APPEND clause is not used, the OUTPUT statement overwrites the contents of the output file by default. The APPEND keyword is valid if the output format is TEXT, FIXED, or SQL.
The BCP format clause is used to import and export files between SQL Anywhere and Adaptive Server Enterprise.
Use this clause to specify whether to include a byte order mark (BOM) at the start of a Unicode file. By default, this option is ON, which directs Interactive SQL to write a byte order mark (BOM) at the beginning of the file. If BYTE ORDER MARK is OFF, DBISQL does not write a BOM.
The BYTE ORDER MARK clause is relevant only when writing TEXT formatted files. Attempts to use the BYTE ORDER MARK clause with FORMAT clauses other than TEXT returns an error.
The BYTE ORDER MARK clause is used only when reading or writing files encoded with UTF-8 or UTF-16 (and their variants). Attempts to use the BYTE ORDER MARK clause with any other encoding returns an error.
The COLUMN WIDTHS clause is used to specify the column widths for the FIXED format output.
Use the CREATE TABLE clause to specify whether to create the destination table if it does not exist. The default is ON.
The DELIMITED BY clause is for the TEXT output format only. The delimiter string is placed between columns. The delimited string is controlled by the isql_field_separator_option.
By default, the delimiter is a comma for locales that use a period as the decimal separator, and a semicolon for locales that use a comma as the decimal separator.
The ENCODING clause allows you to specify the encoding that is used to write the file. The ENCODING clause can only be used with the TEXT format.
The ENCODING clause is useful when you have data that cannot be represented in the operating system character set. In this case, if you do not use the ENCODING clause, characters that cannot be represented in the default encoding are lost in the output (that is, a lossy conversion occurs).
If the input file was created using the OUTPUT statement and an encoding was specified, then the same ENCODING clause should be specified on the INPUT statement.
When running Interactive SQL, the encoding that is used to export the data is determined in the following order:
The encoding specified by the ENCODING clause (if this clause is specified)
The encoding specified with the default_isql_encoding option (if this option is set).
The default encoding for the platform you are running on. On English Windows computers, the default encoding is 1252.
The default escape character for characters stored as hexadecimal codes and symbols is a backslash (\). For example, \x0A is the line feed character.
This setting can be changed using the ESCAPE CHARACTER clause. For example, to use the exclamation mark as the escape character, specify:
... ESCAPE CHARACTER '!'
The new line character can be specified as '\n'. Other characters can be specified using hexadecimal ASCII codes, such as \x09 for the tab character. A sequence of two backslash characters ( \\ ) is interpreted as a single backslash. A backslash followed by any character other than n, x, X, or \ is interpreted as two separate characters. For example, \q is interpreted as a backslash and the letter q.
With ESCAPES turned on (the default), characters following the backslash character are recognized and interpreted as special characters by the database server. With ESCAPES turned off, the characters are written exactly as they appear in the source data.
The FORMAT clause allows you to specify the file format for the output. If you do not specify the FORMAT clause, the format specified by the output_format option is used. If you specify the FORMAT clause, the setting of the output_format option is ignored. The default output format is TEXT. Allowable output formats are:
The output is a TEXT format file with one row per line in the file. All values are separated by a field delimiter, and strings are enclosed in apostrophes (single quotes). Field delimiters are typically commas (,) or semicolons (;). The delimiter and quote strings can be changed using the DELIMITED BY and QUOTE clauses.
Three other special sequences are also used. The two characters \n represent a newline character, \\ represents a single \, and the sequence \xDD represents the character with hexadecimal code DD.
To TEXT without including quotes or newlines in your output, turn off quotes and escapes as follows: QUOTE '' ESCAPES OFF.
When files with a .csv or .txt extension are exported with the FORMAT EXCEL clause, they follow the default formatting for Microsoft Excel files. If the file name does not have a .txt or .csv file extension, then the output is a Microsoft Excel worksheet with a .xlsb extension. Column headings are written to the first row on the sheet.
When exporting to a Microsoft Excel file, the Microsoft Excel ODBC driver must be installed, and the bitness of Interactive SQL and the Microsoft Excel ODBC driver must match.
Exporting to a Microsoft Excel workbook (.xl*) file is only supported on Windows.
The output is fixed format with each column having a fixed width. The width for each column can be specified using the COLUMN WIDTHS clause. No column headings are output in this format.
If the COLUMN WIDTHS clause is omitted, the width for each column is computed from the data type for the column, and is large enough to hold any value of that data type. The exception is that LONG VARCHAR and LONG BINARY data default to 32 KB.
The output is in the Hyper Text Markup Language format.
The output is an Interactive SQL INPUT statement (required to recreate the information in the table) in a .sql file.
The output is an XML file encoded in UTF-8 and containing an embedded DTD. Binary values are encoded in CDATA blocks with the binary data rendered as 2-hex-digit strings.
The HEXADECIMAL clause specifies how binary values are output for the TEXT format. Allowable values are:
When set to ON, binary values are written with an Ox prefix followed by a series of hexadecimal pairs; for example, 0xabcd.
When set to OFF, unprintable character values are prefixed with the escape character, such as a backslash, followed by an x, and then followed by the hexadecimal pair for the byte. Printable characters are output as-is.
When set to ASIS, values are written as is, without any escaping, even if the values contain control characters. ASIS is useful for text that contains formatting characters such as tabs or carriage returns.
The QUOTE clause is for the TEXT output format only. The quote string is placed around string values. The default is a single quote ('). If ALL is specified in the QUOTE clause, the quote string is placed around all values, not just around strings. To suppress quoting, specify empty single quotes. For example, QUOTE ''.
The USING clause exports data to an ODBC data source. You can either specify the ODBC data source name with the DSN option, or the ODBC driver name and connection parameters with the DRIVER option. Connection-parameter is an optional list of database-specific connection parameters.
odbc-data-source is the name of a user or ODBC data source name. For example, odbc-data-source for the SQL Anywhere sample database is SQL Anywhere 17 Demo.
odbc-driver-name is the ODBC driver name. For a SQL Anywhere database, the odbc-driver-name is SQL Anywhere; for an UltraLite database, odbc-driver-name is UltraLite 17.
When the optional VERBOSE keyword is included, error messages about the query, the SQL statement used to select the data, and the data itself are written to the output file. Lines that do not contain data are prefixed by two hyphens. If VERBOSE is omitted (the default) only the data is written to the file. The VERBOSE keyword is valid if the output format is TEXT, FIXED, or SQL.
The WITH COLUMN NAMES clause inserts the column names in the first line of the text file. The WITH COLUMN NAMES clause is for TEXT format only. For Microsoft Excel files, the column headings are written to the first row in the file.
When the FORMAT clause is set to EXCEL, the WORKSHEET clause specifies the worksheet within the Microsoft Excel file that data is exported to. If the clause is omitted, then data is exported to a default worksheet named Results.
The OUTPUT statement is used directly after a statement that retrieves the data to be output.
The OUTPUT statement with its clauses APPEND and VERBOSE is equivalent to the >#, >>#, >&, and >>& operators of earlier versions of Interactive SQL. These operators redirect data, but the Interactive SQL statements allow for more precise output and easier-to-read code.
If the executed statement returns multiple result sets, then Interactive SQL creates a file for each result set. The files are named filename- x, where x is a counter starting at 1.
You cannot use the syntax for outputting to an ODBC data source to export multiple result sets. Multiple result sets cannot be exported to a Microsoft Excel workbook.
The output format can be specified with the optional FORMAT clause. The default format is TEXT. If no FORMAT clause is specified, then the Interactive SQL output_format option setting is used.
Because the INPUT statement is an Interactive SQL statement, you cannot use it in any compound statement (such as an IF statement), in a stored procedure, or in any statement executed by the server.
Microsoft Excel does not support BINARY or LONG BINARY data, so the data must be converted to a string or number before it is exported.
None.
In Interactive SQL, the Results tab displays the results of the current query.
Not in the standard.
Place the contents of the Employees table in a text file:
SELECT * FROM GROUPO.Employees; OUTPUT TO 'c:\\temp\\Employees.txt' FORMAT TEXT;
Place the contents of the Employees table at the end of an existing text file, and include any messages about the query in this file as well:
SELECT * FROM GROUPO.Employees; OUTPUT TO 'c:\\temp\\Employees.txt' APPEND VERBOSE;
Suppose you want to export a value that contains an embedded line feed character. A line feed character has the numeric value 10, which you can represent as the string \x0a in a SQL statement. For example, execute the following statement, with HEXADECIMAL set to ON:
SELECT CAST ('line1\x0aline2' AS VARBINARY);
OUTPUT TO 'c:\\temp\\file.txt' HEXADECIMAL ON;You get a file with one line in it containing: 0x6c696e65310a6c696e6532.
If you execute the same statement with HEXADECIMAL set to OFF, then you get: 'line1\x0Aline2'.
Finally, if you set HEXADECIMAL to ASIS, you get a file with two lines:
'line1 line2'
You get two lines when you use ASIS because the embedded line feed character has been exported without being converted to a two digit hexadecimal representation, and without being prefixed by anything.
The following example outputs the data from the Customers table to a new table, Customers2:
SELECT * FROM Customers;
OUTPUT USING 'DSN=SQL Anywhere 17 Demo;PWD=sql'
INTO "Customers2";The following example copies the Customers table from the sample database to a fictitious database called mydatabase.db, using the DRIVER option.
SELECT * FROM Customers; OUTPUT USING 'DRIVER=SQL Anywhere 17;UID=DBA;PWD=passwd;DBF=c:\\test\\mydatabase.db' INTO "Customers";
The following example copies the Customers table from the SQL Anywhere sample database into a table called Customers in a fictitious UltraLite database, myULDatabase.db, using the DRIVER option.
SELECT * FROM Customers;
OUTPUT USING 'DRIVER=UltraLite 17;DBF=c:\\test\\myULDatabase.udb'
INTO "Customers";The following example copies the Customers table into a fictitious MySQL database called mydatabase, using the DRIVER option.
SELECT * FROM GROUPO.Customers; OUTPUT USING 'DRIVER=MySQL ODBC 5.1 Driver;DATABASE=mydatabase;SERVER=mySQLHost;UID=me;PWD=secret' INTO "Customers";
The following command outputs a file which contains 'one\x0Atwo\x0Athree':
SELECT 'one\ntwo\nthree'; OUTPUT TO 'c:\\temp\\test.txt' HEXADECIMAL OFF;
The following example exports the Customers table into a Microsoft Excel workbook called customers.xlsb:
SELECT * FROM Customers; OUTPUT TO 'Customers.xlsb' FORMAT EXCEL