Import data with the INPUT statement

Use the INPUT statement to import data in different file formats into existing or new tables. If you have the ODBC drivers for the databases, use the USING clause to import data from different types of databases, and from different versions of SQL Anywhere databases.

With the INPUT statement, you can import data from TEXT and FIXED formats. To import data from another file format, use the USING clause with an ODBC data source.

You can use the default input format, or you can specify the file format for 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 import data from a file, or from another database.

For more information, see INPUT statement [Interactive SQL].

Considerations for materialized views

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 remains 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.

Considerations for text indexes

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.

Impact on the database

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.

To import data (INPUT statement)

  1. 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'

  2. Open Interactive SQL and connect to the SQL Anywhere 11 Demo database.

  3. 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, the name of the destination table in the SQL Anywhere 11 Demo database is Employees, and new_employees.txt is the name of the source file.

  4. 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.

To import data from a Microsoft Excel spreadsheet into a SQL Anywhere database

  1. Open the spreadsheet in Microsoft Excel.

  2. In Microsoft Excel, select the cells you want to import, then choose Insert » Name » Define.

    Type a name, such as myData, for the selected cells.

  3. Click OK.

  4. Save and close the spreadsheet.

  5. Create an ODBC data source for the spreadsheet.

    • Choose Start » Programs » SQL Anywhere 11 » ODBC Administrator.

    • Select the User DSN tab to create a DSN for the current user or select the System DSN tab to create a system-wide DSN.

    • Click Add.

      From the list of drivers, choose the Microsoft Excel Driver, and then click Finish.

    • Specify the parameters you need, click OK to close the window and create the data source.

      For example, type myExcelFile in the Data Source Name field. Click Select Workbook and browse to find your Excel spreadsheet file.

    • Click OK to save the DSN.

  6. Open Interactive SQL and connect to a SQL Anywhere database.

  7. 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
Copyright © 2009, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.1