Product Documentation

FairCom ISQL

Previous Topic

Next Topic

The FOR EACH Statement

The FOR EACH statement scans for each valid record in the data file and inserts the record into the database. The syntax for the FOR EACH statement is shown below:


FOR EACH RECORD record_name FROM "data_file_name", ...

INSERT INTO owner_name.target_table [ (field_name, ...) ]

VALUES (value, ...) ;

NEXT RECORD

The following are the variable descriptions of the FOR EACH statement:

  • record_name is the record name that is specified in the DEFINE RECORD statement.
  • data_file_name is the name of the input data file name. The file name must be enclosed in double quotation marks, for example: "myfile"
  • owner_name.target_table is the target table name identified along with the owner name of the table. The target_table must already exist in the database and must have appropriate permissions for inserting the records.
  • field_name is the name of the field or column in the table.
  • value is the value that must be inserted into the table.

The target_table can also be a synonym on another table with the INSERT access. The list of values that are to be inserted must follow the VALUES keyword. The values that can be inserted are:

  • Name of the field in the input data file
  • A constant (both numeric as well as character)
  • NULL

The values specified in the VALUES list must correspond one to one with that in the target table list. The list can be in any order compared to the list specified in the DEFINE RECORD statement. The following example shows the list interchanged with respect to the list in the DEFINE RECORD statement.


DEFINE RECORD dept_rec AS

( dept_no, dept_name, location ) FIELD DELIMITER ' ' ;


FOR EACH RECORD dept_rec FROM dept_in

INSERT INTO ADMIN.department (loc, no, name)

VALUES (location, dept_no, dept_name) ;


NEXT RECORD

Here the items no, name, and loc are interchanged in both the table list and the values list when compared with the DEFINE RECORD list.

The keyword NEXT RECORD must be specified after the FOR EACH statement so that the insert loop is terminated.

TOCIndex