Inserting rows

You can insert data into segments using the SQL INSERT statement.

Use an INSERT statement to add new rows to a segment or view. Using an INSERT statement, you can specify the field values to insert a single row. You can specify constants or parameter markers, by using the VALUES clause.

For every row that you insert, you must provide a value for every key field. If you do not specify a value in the INSERT call, IMS sets a value of 0.

Inserting a single row:

You can use the VALUES clause of the INSERT statement to insert a single row of field values into a segment. You can either name all of the fields for which you are providing values, or you can omit the list of field names. If you omit the field name list, you must specify values for all of the fields. The fields are ordered first by their field position within the IMS catalog and then by their length.

When inserting a record in a table at a non-root level, you must specify values for all the foreign key fields of the table. Foreign key fields properly position the new record (or segment instance) to be inserted in the hierarchic path using standard SQL processing, similar to foreign keys in a relational database.

Recommendation: For INSERT statements, name all of the fields for which you are providing values for the following reasons:
  • Your INSERT statement is independent of the segment format. (For example, you do not need to change the statement when a field is added to the segment.)
  • You can verify that you are specifying the values in order.
  • Your source statements are more self-descriptive.

When you list the field names, you must specify their corresponding values in the same order as in the list of field names.

Example: The following statement inserts information about a new hospital into the HOSPITAL segment. 
INSERT INTO PCB01.HOSPITAL (HOSPCODE, HOSPNAME)
  VALUES ('R1210050000A', 'MALLEY CLINIC')

After inserting a new hospital into your HOSPITAL segment, you can use a SELECT statement to see what you have loaded into the segment. The following SQL statement shows you all of the new department rows that you have inserted: 

SELECT HOSPCODE, HOSPNAME
  FROM PCB01.HOSPITAL

The result segment looks similar to the following output: 

+------------+-----------------+
|HOSPCODE    |HOSPNAME         |
+------------+-----------------+
|R1210010000A|ALEXANDRIA       |
|R1210020000A|SANTA TERESA     |
|R1210030000A|SANTA CLARA      |
|R1210040000A|NEW ENGLAND      |
|R1210050000A|MALLEY CLINIC    |
Example: The following statement inserts a new WARD record under a specific HOSPITAL table. In this example, the WARD table has the foreign key HOSPITAL_HOSPCODE. The new record will be inserted if and only if there is a HOSPCODE in the HOSPITAL table with the value of 'R1210050000A'
INSERT INTO PCB01.WARD
  WARDNO, HOSPITAL_HOSPCODE, WARDNAME)
  VALUES ('0001', 'R1210050000A', 'EMGY')
Example: The following statement also inserts a row into the HOSPITAL segment without specifying the column names. All the columns values must be specified in the VALUES clause. 
INSERT INTO PCB01.HOSPITAL
  VALUES (900, 'R1210050000A', 'MALLEY CLINIC');