input

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

Syntax

input into [ owner.]table_name
	[ from filename | prompt]
	[ format { ascii | dbase | dbasell | dbaselll | excel | fixed | foxpro | lotus }]
	[ escape character character]
	[ escapes { on | off }
	[ by order | by name ]
	[ delimited by string ]
	[ column widths (integer , . . . ) ]
	[ nostrip ]
	[ ( column_name, . . . ) ]
	[ encoding {identifier | string}]

Parameters

Examples

Usage

  • 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 you specify from file_name). 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. In this case, input is ended with a line containing only the string end.

  • 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 in which 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.

    In this example, you create a table called inventory. To import ASCII data from the input file that contains the name value before the quantity value, you must list the input file’s actual column order at the end of the input statement for the data to be inserted correctly:
    create table inventory (
    quantity int,
    item varchar(60)
    )
    The ASCII data from the input file stock.txt that contains the name value before the quantity value:
    'Shirts', 100
    'Shorts', 60
    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, input 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. Interactive SQL prints a warning in the Messages pane 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.

Permissions

You must have insert permission on the table or view.