|
Imports data into a database table from an external file, from the keyboard, from an ODBC data source, or from a shapefile.
Import from an external file or from the keyboard
INPUT INTO [ owner.]table-name input-options
input-options : [ ( column-name, ... ) ] [ BYTE ORDER MARK { ON | OFF } ] [ COLUMN WIDTHS ( integer, ...) ] [ DELIMITED BY string ] [ ENCODING encoding ] [ ESCAPE CHARACTER character ] [ ESCAPES { ON | OFF } ] [ FORMAT input-format ] [ FROM filename | PROMPT ] [ NOSTRIP ] [ SKIP integer ]
input-format : TEXT | FIXED
encoding : identifier | string
Import from an ODBC data source
INPUT USING connection-string FROM source-table-name INTO destination-table-name [ CREATE TABLE { ON | OFF } ]
connection-string :
{ DRIVER=odbc-driver-name
| DSN=odbc-data-source } [ ; { connection-parameter = value } ]Import from a shapefile
INPUT INTO [ owner.]table-name FROM filename FORMAT SHAPEFILE [ SRID srid-number ] [ ENCODING encoding ]
encoding : identifier | string
List the names of the columns in the table in the order that corresponds to the order of the values in the input file. Use this parameter when the order of the table columns is different from the order of the values in the input file, or there are fewer columns in the input file than there are in the table.
Use this clause to specify whether to process a byte order mark (BOM) in the input file.
The BYTE ORDER MARK clause is relevant only when reading from TEXT formatted files. Attempts to use the BYTE ORDER MARK clause with FORMAT clauses other than TEXT causes an error.
The BYTE ORDER MARK clause is used only when reading or writing files encoded with UTF-8 or UTF-16 (and their variants). Attempts to use the BYTE ORDER MARK clause with any other encoding causes an error.
If the ENCODING clause is specified:
If the ENCODING clause is not specified:
The COLUMN WIDTHS clause specifies the widths of the columns in the input file and can only be specified for FIXED format. If COLUMN WIDTHS is not specified, the widths are determined by the database column types.
Use the CREATE TABLE clause to specify whether to create the destination table if it does not exist. The default is ON. The CREATE TABLE ON clause can only be used when importing from an ODBC data source.
The DELIMITED BY clause is only supported for the TEXT input format. This clause allows you to specify a string to be used as the delimiter between column values in the TEXT input format.
The default delimiter is a comma for locales that use a period as the decimal separator, and a semicolon for locales that use commas as the decimal separator.
The encoding argument specifies the encoding that is used to read the file. The ENCODING clause can only be used with the TEXT and SHAPEFILE formats.
When running Interactive SQL, the encoding that is used to import the data is determined in the following order:
The encoding specified by the ENCODING clause (if this clause is specified).
The encoding specified by the default_isql_encoding option (if this option is set).
If the file has a byte-order mark, the appropriate Unicode encoding is used.
The default encoding for the computer you are running on. On English Windows computers, the default encoding is 1252 or 850.
If the input file was created using the OUTPUT statement and an encoding was specified, then the same ENCODING clause should be specified on the INPUT statement.
The default escape character for hexadecimal codes is a backslash (\). For example, \x0A is the line feed character.
The newline character can be specified as \n. Other characters can be specified using 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 is interpreted as 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, specify:
... ESCAPE CHARACTER '!'
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.
The FORMAT clause allows you to specify the file format for the input. If you do not specify the FORMAT clause, the format specified by the input_format option is used. If you specify the FORMAT clause, the setting of the input_format option is ignored. The default input format is TEXT. Allowable input formats are:
Input lines are assumed to be characters, one row per line, with column values separated by delimiters. Alphabetic strings may be enclosed in single quotes or double quotes. Strings containing delimiters 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. Use the DELIMITED BY clause to specify a field separator.
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.
Omitted values are treated as NULL. If the value in that position cannot be NULL, a zero is inserted in numeric columns and an empty string in character columns.
When files with a .txt or .csv extension are imported with the FORMAT EXCEL clause, they follow the default formatting for Microsoft Excel workbook files.
The default worksheet used is the first worksheet in the file. To specify a specific worksheet, use the WORKSHEET clause. The first row in the worksheet is assumed to contain the column headings.
Input from a workbook file (.xl*) is only supported on Windows.
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 allowed by the corresponding database column.
The FIXED format cannot be used with binary columns that contain embedded newline and end-of-file character sequences.
Input is in the form of an ESRI shapefile. Unlike the LOAD statement, when loading shapefiles using the INPUT statement, the shapefile must be located on the client computer. The associated .shx and .dbf files must be located in the same directory as the .shp file, and have the same base file name.
If an encoding is not specified when loading a shapefile, the default is ISO-8859-1.
Use the SRID clause to specify a SRID to associate with the geometries. If you do not specify a SRID, SRID 0 is used by default. Ideally, you should specify the same SRID as the one mentioned in the project file (.prj) for the shapefile. If that SRID is not available, use one that is equivalent. SQL Anywhere provides thousands of SRIDs you can add to the database using the sa_install_feature system procedure.
To use other formats such as, DBASE II, DBASE III, Microsoft Visual FoxPro, Lotus 123, or Microsoft Excel 97, use INPUT USING.
The filename can be quoted or unquoted. If the string is quoted, it is subject to the same formatting requirements as other SQL strings.
To indicate directory paths, the backslash character (\) must be represented by two backslashes when the path is quoted.
The location of a relative filename is determined as follows:
If the INPUT statement is executed directly in Interactive SQL, the path to filename is resolved relative to the directory in which Interactive SQL is running. For example, suppose you open Interactive SQL from the directory c:\work and execute the following statement:
INPUT INTO GROUPO.Employees FROM 'inputs\\inputfile.dat';
Interactive SQL looks for c:\work\inputs\inputfile.dat.
If the INPUT statement resides in a .sql file, Interactive SQL first attempts to resolve the path to filename relative to the location of the file. If unsuccessful, Interactive SQL looks for filename in a path relative to the directory in which Interactive SQL is running.
For example, suppose you had a file, c:\homework\inputs.sql, that contained the following statement:
INPUT INTO GROUPO.Employees FROM 'inputs\\inputfile.dat';
Interactive SQL would first look for inputfile.dat in c:\homework\inputs. If Interactive SQL does not find inputfile.dat in that location, Interactive SQL looks in the directory in which Interactive SQL is running.
The source-table-name parameter is a quoted string containing the name of the table in the source database. The name can be in the form database-name.owner.table-name, owner.table-name, or table-name. Use a period to separate the components, even if that is not the native separator in the source database. If the source database requires a database name, but not an owner name, the format of source-table-name must be database..table (in this case the owner name is empty). Do not quote any of the names in the parameter. For example, do not use 'dba."my-table"'; use 'dba.my-table' instead.
The name of the table to input the data into.
The PROMPT clause allows the user to enter values for each column in a row. When running in windowed mode, a window is displayed, allowing the user to enter the values for the new row. If you are running Interactive SQL from the command line, Interactive SQL prompts you to type the value for each column on the command line.
Normally, for TEXT 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.
When inserting lines from a TEXT file, the SKIP clause omits the specified number of lines starting at the beginning of the file. The number specified must be a non-negative integer. The SKIP clause is for TEXT format only.
If the specified number of lines exceeds the number of lines in the file, the INPUT statement inserts no data and no error is returned.
The USING clause inputs data from an ODBC data source. You can either specify the ODBC data source name with the DSN option, or the ODBC driver name and connection parameters with the DRIVER option. The connection-parameter parameter is a list of database-specific connection parameters.
The odbc-data-source parameter is the name of a user or ODBC data source name. For example, odbc-data-source for the SQL Anywhere sample database is SQL Anywhere 17 Demo.
The odbc-driver-name parameter is the ODBC driver name. For a SQL Anywhere database, odbc-driver-name is SQL Anywhere 17. For an UltraLite database, odbc-driver-name is UltraLite 17.
The WORKSHEET clause identifies the name of the worksheet within the Microsoft Excel file that data is imported from. If the clause is omitted, then the first sheet in the Microsoft Excel file is used.
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 filename is specified). If neither is specified, the input is read from the SQL script file that contains the INPUT statement. In Interactive SQL, this can even be directly read from the SQL Statements pane. In this case, input is ended with a line containing only the string END.
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 keyword (not a semicolon) terminates data for INPUT statements that do not name a file and do not include the PROMPT keyword.
If a column list is specified, 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 table column order is different, you must use column-name parameter to list the table columns in the same order as the column values in the input file.
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.
Interactive SQL displays a warning on the History 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 statement, you cannot use it in any compound statement (such as an IF statement), in a stored procedure, or in any statement executed by the database server.
You must be the owner of the table, or have the INSERT ANY TABLE system privilege, or have INSERT privilege on the table. You must also have the SELECT ANY TABLE system privilege, or have SELECT privilege on the table.
None.
Not in the standard.
This example imports data into the Employees table from a fictitious TEXT file, new_emp.inp:
INPUT INTO GROUPO.Employees FROM new_emp.inp FORMAT TEXT;
This example copies the table ulTest, into a table called saTest. The ulTest table is in an UltraLite database in the file C:\test\myULDatabase.udb, and the saTest table is created in demo.db:
INPUT USING 'driver=UltraLite 17;dbf=C:\\test\\myULDatabase.udb'
FROM "ulTest" INTO "saTest";This example loads the shapefile myshapefile.shp into the myTable table, and assigns SRID 4269 to the geometries in the shapefile.
INPUT INTO myTable FROM 'myshapefile.shp' FORMAT SHAPEFILE SRID 4269
This example adds a new row to the Products table, and prompts the user to enter the values for each column.
INPUT INTO GROUPO.Products PROMPT;
This example loads data from the file c:\temp\input.dat into the Employees table. Note how the backslashes are doubled.
INPUT INTO GROUPO.Employees FROM 'c:\\temp\\input.dat';
The following example creates a table, myInventory, and imports data from the file stock.txt that contains the data but in a different column order than the table definition. To correct the order mismatch, the correct column order required for the import is specified by the column-name parameter after the table name in the INPUT statement. That is, (item, Quantity) instructs Interactive SQL to take the first column value in the input file and place it in the second column of the table, and then take the second column value in the input file and place it in the first column of the table.
CREATE TABLE myInventory ( Quantity INTEGER, item VARCHAR(60) ); INPUT INTO myInventory (item, Quantity) FROM stock.txt;
INPUT INTO inputTest FROM 'c:\\test\\Book1.xlsx' FORMAT EXCEL