The INPUT statement lets you import data in a variety of file formats a table. You can choose to import this data into an existing table, or you can create and configure a completely new table. You can use the USING clause to import data from different types of databases as well as from different versions of SQL Anywhere databases (as long as you have the ODBC drivers for the databases).
The INPUT statement lets you import data from TEXT and FIXED formats. If you want to import data from another file format, you can use the USING clause with an ODBC data source to import the data.
You can either use the default input format, or you can specify the file format on each INPUT statement. Because the INPUT statement is an Interactive SQL command, you cannot use it in any compound statement (such as an IF statement) or in a stored procedure.
Use the INPUT statement to import data when you want to:
For more information, see INPUT statement [Interactive SQL].
For immediate views, an error is returned when you attempt to bulk load data into an underlying table. You must truncate the data in the view first, and then perform the bulk load operation.
For manual views, you can bulk load data into an underlying table; however, the data in the view becomes stale until the next refresh.
Consider truncating data in dependent materialized views before attempting a bulk load operation such as INPUT on a table. After you have loaded the data, refresh the view. See TRUNCATE statement, and REFRESH MATERIALIZED VIEW statement.
For immediate text indexes, updating the text index after performing a bulk load operation such as INPUT on the underlying table can take a while even though the update is automatic. For manual text indexes, even a refresh can take a while.
Consider dropping dependent text indexes before performing a bulk load operation such as INPUT on a table. After you have loaded the data, recreate the text index. See DROP TEXT INDEX statement, and CREATE TEXT INDEX statement.
Changes are recorded in the transaction log when you use the INPUT statement. In the event of a media failure, there is a detailed record of the changes. However, there are performance impacts associated with importing large amounts of data with this method since all rows are written to the transaction log.
In comparison, the LOAD TABLE statement does not save each row to the transaction log and so it can be faster than the INPUT statement. However the INPUT statement is more flexible in terms of the databases and file formats that it supports.
This example shows you how to import data using the INPUT statement.
To import data (INPUT statement)
Create and save a text file named new_employees.txt with the following values (on a single line):
101,500,'Chan','Julia',100,'300 Royal Drive', 'Springfield','OR','USA','97015','6175553985', 'A','017239033',55700,'1984-09-29',,'1968-05-05', 1,1,0,'F'
Open Interactive SQL and connect to the SQL Anywhere 11 Demo database.
Enter an INPUT statement in the SQL Statements pane.
INPUT INTO Employees FROM c:\new_employees.txt FORMAT TEXT; SELECT * FROM Employees;
In this statement, Employees is the name of the destination table in the SQL Anywhere 11 Demo database, and new_employees.txt is the name of the source file.
Execute the statement.
If the import is successful, the Messages tab displays the amount of time it to took to import the data. If the import is unsuccessful, a message appears indicating why the import was unsuccessful.
This example shows you how to import data from an Excel spreadsheet into a SQL Anywhere database
To import data from a Microsoft Excel spreadsheet into a SQL Anywhere database
Open the spreadsheet in Microsoft Excel.
In Excel, select the cells you want to import, then choose Insert » Name » Define.
The Define Name window appears.
Type a name, such as myData, for the selected cells.
Save and close the spreadsheet.
Create an ODBC data source for the spreadsheet.
The Create New Data Source Wizard appears.
From the list of drivers, choose the Microsoft Excel Driver, and then click Finish.
The ODBC Microsoft Excel Setup window appears.
For example, type myExcelFile in the Data Source Name field. Click Select Workbook and browse to find your Excel spreadsheet file.
Open Interactive SQL and connect to a SQL Anywhere database.
Execute the following INPUT statement to import the data from the Excel spreadsheet and to save it in a new table called t:
INPUT USING 'dsn=myExcelFile;DSN=myExcelFile' FROM "myData" INTO "t" CREATE TABLE ON
|Send feedback about this page via email or DocCommentXchange||Copyright © 2008, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.0|