Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 10.0.1 » SQL Anywhere Server - SQL Reference » SQL Statements

INCLUDE statement [ESQL] Next Page

INPUT statement [Interactive SQL]


Use this statement to import data into a database table from an external file or from the keyboard.

Syntax

INPUT INTO [ owner.]table-name
[ FROM file-name | PROMPT ]
[ FORMAT input-format ]
[ ESCAPE CHARACTER character ]
[ ESCAPES { ON | OFF }
[ BY ORDER | BY NAME ]
[ DELIMITED BY string ]
[ COLUMN WIDTHS ( integer, ...) ]
[ NOSTRIP ]
[ ( column-name, ... ) ]
[ ENCODING encoding ]

input-format :
ASCII | DBASE | DBASEII | DBASEIII
| EXCEL | FIXED | FOXPRO | LOTUS

encoding : identifier or string

Parameters

INTO clause    The name of the table into which to input the data.

FROM clause    The file-name can be quoted or unquoted. If the string is quoted, it is subject to the same formatting requirements as other SQL strings. In particular:

PROMPT clause    The PROMPT clause allows the user to enter values for each column in a row. When running in windowed mode, a dialog is displayed, allowing the user to enter the values for the new row. If you are running Interactive SQL on the command line, then Interactive SQL prompts you to type the value for each column on the command line.

FORMAT clause    Each set of values must be in the format specified by the FORMAT clause, or the format set by the SET OPTION input_format statement if the FORMAT clause is not specified.

Certain file formats contain information about column names and types. Using this information, the INPUT statement will create the database table if it does not already exist. This is a very easy way to load data into the database. The formats that have enough information to create the table are: DBASEII, DBASEIII, EXCEL, FOXPRO, and LOTUS.

Input from a command file is terminated by a line containing END. Input from a file is terminated at the end of the file.

Allowable input formats are:

ESCAPE CHARACTER clause    The default escape character for hexadecimal codes and symbols is a backslash (\), so \x0A is the linefeed character, for example.

Newline characters can be included as the combination \n, 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.

The escape character can be changed, using the ESCAPE CHARACTER clause. For example, to use the exclamation mark as the escape character, you would enter:

... ESCAPE CHARACTER '!'

ESCAPES clause    With ESCAPES turned on (the default), characters following the escape character are interpreted as special characters by the database server. With ESCAPES turned off, the characters are read exactly as they appear in the source.

BY clause    The BY clause allows the user to specify whether the columns from the input file should be matched up with the table columns based on their ordinal position in the list (ORDER, the default) or by their names (NAME). Not all input formats have column name information in the file. NAME is allowed only for those formats that do. They are the same formats that allow automatic table creation: DBASEII, DBASEIII, EXCEL, FOXPRO, and LOTUS.

DELIMITED BY clause    The DELIMITED BY clause allows you to specify a string to be used as the delimiter in ASCII input format. The default delimiter is a comma.

COLUMN WIDTHS clause    COLUMN WIDTHS can be specified for FIXED format only. It specifies the widths of the columns in the input file. If COLUMN WIDTHS is not specified, the widths are determined by the database column types. This clause should not be used if inserting LONG VARCHAR or BINARY data in FIXED format.

NOSTRIP clause    Normally, for ASCII input format, trailing blanks are stripped from unquoted strings before the value is inserted. NOSTRIP can be used to suppress trailing blank stripping. Trailing blanks are not stripped from quoted strings, regardless of whether the option is used. Leading blanks are stripped from unquoted strings, regardless of the NOSTRIP option setting.

ENCODING clause    The encoding argument allows you to specify the encoding that is used to read the file. The ENCODING clause can only be used with the ASCII format.

For more information on how to obtain the list of SQL Anywhere supported encodings, see Supported character sets.

For Interactive SQL, if encoding is not specified, the encoding that is used to read the file is determined as follows, where encoding values occurring earlier in the list take precedence over those occurring later in the list:

For more information about Interactive SQL and encodings, see default_isql_encoding option [Interactive SQL].

Remarks

The INPUT statement allows efficient mass insertion into a named database table. Lines of input are read either from the user via an input window (if PROMPT is specified) or from a file (if FROM file-name is specified). If neither is specified, the input is read from the command file that contains the INPUT statement—in Interactive SQL, this can even be directly from the SQL Statements pane.

When the input is read directly from the SQL Statements pane, you must specify a semicolon before the values for the records to be inserted at the end of the INPUT statement. For example:

INPUT INTO Owner.TableName;
value1, value2, value3
value1, value2, value3
value1, value2, value3
value1, value2, value3
END;

The END statement terminates data for INPUT statements that do not name a file and do not include the PROMPT keyword.

If a column list is specified for any input format, the data is inserted into the specified columns of the named table. By default, the INPUT statement assumes that column values in the input file appear in the same order as they appear in the database table definition. If the input file's column order is different, you must list the input file's actual column order at the end of the INPUT statement.

For example, if you create a table with the following statement:

CREATE TABLE inventory (
Quantity INTEGER,
item VARCHAR(60)
);

and you want to import ASCII data from the input file stock.txt that contains the name value before the quantity value,

'Shirts', 100
'Shorts', 60

then you must list the input file's actual column order at the end of the INPUT statement for the data to be inserted correctly:

INPUT INTO inventory
FROM stock.txt
FORMAT ASCII
(item, Quantity);

By default, the INPUT statement stops when it attempts to insert a row that causes an error. Errors can be treated in different ways by setting the on_error and conversion_error options (see SET OPTION). Interactive SQL prints a warning on the Messages tab if any string values are truncated on INPUT. Missing values for NOT NULL columns are set to zero for numeric types and to the empty string for non-numeric types. If INPUT attempts to insert a NULL row, the input file contains an empty row.

Because the INPUT statement is an Interactive SQL command, it cannot be used in any compound statement (such as IF) or in a stored procedure.

See Statements allowed in procedures, triggers, events, and batches.

Permissions

Must have INSERT permission on the table or view.

Side effects

None.

See also
Standards and compatibility
Example

The following is an example of an INPUT statement from an ASCII text file.

INPUT INTO Employees
FROM new_emp.inp
FORMAT ASCII;