Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

Using the INPUT statement to import data

The INPUT statement lets you import data in a variety of file formats into one or more tables. You can choose a default input format, or you can specify the file format on each INPUT statement. Interactive SQL can execute a command file containing multiple INPUT statements. 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 into one or more tables

• automate the import process using a command file

• import data in a format other than text

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

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.

The INPUT statement is slower than the LOAD TABLE statement.

If a data file is in DBASE, DBASEII, DBASEIII, FOXPRO, or LOTUS format and the table does not exist, it is created.

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

To import data (Interactive SQL 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. Ensure that the destination table exists.

3. Enter an INPUT statement in the SQL Statements pane of Interactive SQL.

   The following is an example of an INPUT statement from an ASCII text file:

   INPUT INTO Employees
   FROM c:\new_employees.txt
   FORMAT ASCII;
   SELECT * FROM Employees;

   In this statement, Employees is the name of the destination table, 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 the import was unsuccessful.

For information about using the INPUT statement to import data, see INPUT statement [Interactive SQL].