exit  output

Chapter 2: Interactive SQL Commands

input

Description

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

Syntax

input into [ owner.]table-name
	[ from filename | 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 | dbasell| dbaselll
	| excel | fixed | foxpro | lotus

encoding : identifier or string

Parameters

from clause

is the file name that is passed to the server as a quoted string. The string is therefore subject to the same formatting requirements as other SQL strings. In particular:

prompt

allows the user to enter values for each column in a row. When running in windowed mode, a dialog appears where the user can enter the values for the new row. If the user is running Interactive SQL on the command line, Interactive SQL prompts the user to type the value for each column on the command line.

format

each set of values must be in the format specified by the format clause, or the set option input_format statement if the format clause is not specified. When input is entered by the user, a dialog 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 creates 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: dbasell, dbaselll, 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

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

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

... escape character '|'

Only one single-byte character can be used as an escape character.

escapes

with escapes 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, 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.

by

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: dbasell, dbaselll, foxpro, and lotus.

delimited

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. Do not use this clause if you are inserting long varchar or binary data in fixed format.

nostrip

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.

If the ASCII file has entries such that a column appears to be null, it is treated as NULL. If the column in that position cannot be NULL, a zero is inserted in numeric columns, and an empty string in character columns.

encoding

allows you to specify the encoding that is used to read 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 read 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

Is an input statement from an ASCII text file:

input into employee
from new_emp.inp
format ASCII

Usage

Permissions

You must have insert permission on the table or view.





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

View this book as PDF