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 supports more databases and file formats.

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 12 sample 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, the name of the destination table in the SQL Anywhere 12 sample database is Employees, 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.

To input data from an Excel CSV file using the INPUT statement

In Excel, save the data from your Excel file into a comma delimited (CSV) file. For example name the file c:\test\finance_comma_delimited.csv
In Interactive SQL, connect to a SQL Anywhere database such as the demo12 database.
Create a table named imported_sales and add the required columns. You cannot use the CREATE TABLE clause to create a table when inputting from a csv file.

Execute an INPUT statement using the SKIP clause to skip over the column names that Excel places in the first line in the CSV file.

INPUT INTO "imported_sales" FROM 'c:\\test\\finances.csv' SKIP 1

Discuss this page in DocCommentXchange.