input  parameters

Chapter 2: Interactive SQL Commands

output

Description

Imports data into a database table from an external file or from the keyboard.

Syntax

output to filename
	[ append ]
	[ verbose ]
	[ format output-format ]
	[ escape character character ]
	[ escapes { on | off}
	[ delimited by string ]
	[ quote string [ all ] ]
	[ column widths (integer , . . . ) ]
	[ hexidecimal { on | off | asis } ]
	[ encoding encoding ]

output-format :
	ascii | dbase | dbasell| dbaselll 
		| excel | fixed | foxpro | lotus | sql | xml

encoding : string or identifier

Parameters

append

appends 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 ASCII, fixed, or SQL.

verbose

Writes error messages about the query, the SQL statement used to select the data, and the data itself to the output file. Lines that do not contain data are prefixed by two hyphens. If you omit verbose (the default) only the data is written to the file. verbose is valid if the output format is ASCII, fixed, or SQL. Allowable output formats are:

escape character

is the default escape character for characters\ stored as hexadecimal codes and symbols is a backslash (\), so, for example, \x0A is the linefeed character.

You can change the default escape character using escape character. For example, to use the exclamation mark as the escape character, enter:

... escape character '!'
escapes

if enabled (the default), characters following the backslash character are recognized and interpreted as special characters by the database server. New line characters can be included as the combination \n, and other characters can be included in data as 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 inserts a backslash and the letter q.

delimited by

for the ASCII output format only. The delimiter string is placed between columns (default comma).

quote

for the ASCII output format only. The quote string is placed around string values. The default is a single quote character. If all is specified in the quote clause, the quote string is placed around all values, not just around strings.

column width

specifies the column widths for the fixed format output

hexidecimal

specifies how binary data is to be unloaded for the ASCII format only. When set to on, binary data is unloaded in the format 0xabcd. When set to off, binary data is escaped when unloaded (\xab\xcd). When set to asis, values are written as is, that is, without any escaping–even if the value contains control characters. asis is useful for text that contains formatting characters such as tabs or carriage returns.

encoding

allows you to specify the encoding that is used to write the file. encoding can be used only with the ASCII format.

If encoding is not specified, Interactive SQL determines the code page that is used to write the file as follows, where code page values occurring earlier in the list take precedence over those occurring later in the list:

Examples

Example 1

Places the contents of the employee table in a file in ASCII format:

select *
    from employee
go
output to employee.txt
    format ASCII

Example 2

Place the contents of the employee table at the end of an existing file, and includes any messages about the query in this file as well:

select *
    from employee
go
output to employee.txt append verbose

Example 3

In this example, you need 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. If you execute the following statement, with hexidecimal set to on:

select 'line1 n x0aline2'
go
output to file.txt hexidecimal on

You see a file with one line in it containing the following text:

line10x0aline2

However, if you execute the same statement with hexidecimal set to off, you see the following:

line1 n x0aline2

Finally, if you set hexidecimal to asis, you see 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 hexidecimal representation, and without being prefixed by anything.

Usage

Permissions

Any user can execute this command.




Copyright © 2005. Sybase Inc. All rights reserved. parameters

View this book as PDF