![INCLUDE statement [ESQL]](gif/backward.gif)
Reference Manual
CHAPTER 9. SQL Statements
To import data into a database table from an external file or from the keyboard.
INPUT INTO [ owner.]table-name
... [ FROM filename | PROMPT ]
... [ FORMAT input-format ]
... [ ESCAPE CHARACTER character ]
... [ BY ORDER | BY NAME ]
... [ DELIMITED BY string ]
... [ COLUMN WIDTHS (integer,...) ]
... [ NOSTRIP ]
... [ ( column-name, ... ) ]
Must have INSERT permission on the table or view.
None.
The INPUT statement allows efficient mass insertion into a 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 filename is specified). If neither is specified, the input will be read from the command file that contains the input statement—this can even be directly from the Interactive SQL editor. In this case, input is ended with a line containing only the string END.
These lines are inserted into the named table. If a column list is specified, the data is inserted into the specified columns of the named table.
Normally, 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 will print a warning in the statistics window if any string values are truncated on INPUT. Missing values for NOT NULL columns will be set to zero for numeric types and to the empty string for non-numeric types.
The default escape character for hexadecimal codes and symbols is a backslash (\), so \x0A is the linefeed character, for example.
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 '!'
Only one single-byte character can be used as an escape character.
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 lists (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 listed below: DBASEII, DBASEIII, DIF, FOXPRO, LOTUS, and WATFILE.
The DELIMITED BY clause allows you to specify a string to be used as the delimiter in ASCII input format.
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.
Normally, for ASCII input format, trailing blanks will be 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.
If the ASCII file has entries such that a column appears to be null, LOAD TABLE treats it as null. If the column in that position cannot be null, it inserts a zero in numeric columns and an empty string in character columns.
Each set of values must occupy one input line and must be in the format specified by the FORMAT clause, or the format set by the SET INPUT_FORMAT statement if the FORMAT clause is not specified. When input is entered by the user, an empty screen is provided for the user to enter one row per line in the input format.
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, DIF, FOXPRO, LOTUS, and WATFILE.
Allowable input formats are:
ASCII Input lines are assumed to be ASCII characters, one row per line, with values separated by commas. Alphabetic strings may be enclosed in apostrophes (single quotes) or quotation marks (double quotes). Strings containing commas must be enclosed in either single or double quotes. If the string itself contains single or double quotes, double the quote character to use it within the string. Optionally, you can use the DELIMITED BY clause to specify a different delimiter string than the default, which is a comma.
Three other special sequences are also recognized. The two characters \n represent a newline character, \\ represents a single (\), and the sequence \xDD represents the character with hexadecimal code DD.
DBASE The file is in dBASE II or dBASE III format. Interactive SQL will attempt to determine which format, based on information in the file. If the table doesn't exist, it will be created.
DBASEII The file is in dBASE II format. If the table doesn't exist, it will be created.
DBASEIII The file is in dBASE III format. If the table doesn't exist, it will be created.
DIF Input file is in Data Interchange Format. If the table doesn't exist, it will be created.
FIXED Input lines are in fixed format. The width of the columns can be specified using the COLUMN WIDTHS clause. If they are not specified, column widths in the file must be the same as the maximum number of characters required by any value of the corresponding database column's type.
The FIXED format cannot be used with binary columns that contain embedded newline and End of File character sequences.
FOXPRO The file is in FoxPro format (the FoxPro memo field is different than the dBASE memo field). If the table doesn't exist, it will be created.
LOTUS The file is a Lotus WKS format worksheet. INPUT assumes that the first row in the Lotus WKS format worksheet is column names. If the table doesn't exist, it will be created. In this case, the types and sizes of the columns created may not be correct because the information in the file pertains to a cell, not to a column.
WATFILE The input is a WATFILE file. If the table doesn't exist, it will be created.
Input from a command file is terminated by a line containing END. Input from a file is terminated at the end of the file.
SQL/92 Vendor extension
Sybase Not applicable
The following is an example of an INPUT statement from an ASCII text file.
INPUT INTO employee FROM new_emp.inp FORMAT ascii;