Using the INPUT statement to import data

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:

  • import data from a file
  • import data 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 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.

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.

Example

This example shows you how to import data using the INPUT statement.

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

  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.

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

  1. Open the spreadsheet in Microsoft Excel.

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

    Click OK.

  3. Save and close the spreadsheet.

  4. Create an ODBC data source for the spreadsheet.

    • Start the ODBC Administrator by choosing 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.

      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.

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

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

  6. 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 © 2008, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.0