INSERT statement usage

The INSERT statement is used to insert new rows into a table.

Foreign key fields enable the IMS™ Universal JDBC driver to 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. 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.

Examples of valid IMS Universal JDBC driver INSERT statements

Inserting data at the root

The following statement inserts a new HOSPITAL record:

INSERT INTO PCB01.HOSPITAL (HOSPCODE, HOSPNAME) 
VALUES ('R1210050000A', 'O''MALLEY CLINIC')

Inserting data into a specified table in a hierarchic path

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. The following statement inserts a new ILLNESS record under a specific HOSPITAL, WARD, and PATIENT table. In this example, the ILLNESS table has three foreign keys HOSPITAL_HOSPCODE, WARD_WARDNO, and PATIENT_PATNUM. The new record will be inserted if and only if there is a HOSPCODE in the HOSPITAL table with the value of 'H5140070000H', a WARD table with a WARDNO value of '01', and a PATIENT table with PATNUM value of 'R1210050000A'.

INSERT INTO PCB01.ILLNESS (HOSPITAL_HOSPCODE, WARD_WARDNO, 
   ILLNAME, PATIENT_PATNUM) 
VALUES ('H5140070000H', '01', 'COLD', 'R1210050000A')

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 'H5140070000H'.

INSERT INTO PCB01.WARD (WARDNO, HOSPITAL_HOSPCODE, WARDNAME) 
VALUES ('0001', 'H5140070000H', 'EMGY')

Inserting data in a searchable field with subfields

If a searchable field consists of subfields, you can insert data by setting all the subfield values such that the searchable field is completely populated.

Examples of invalid IMS Universal JDBC driver INSERT statements

Example of a valid IMS classic JDBC driver INSERT query that is invalid for the IMS Universal JDBC driver

This statement is invalid for the IMS Universal JDBC driver because it does not use the correct syntax to specify a legal value for the foreign key field (HOSPITAL_HOSPCODE). Note that it is not standard SQL to use a WHERE clause on an INSERT statement.

INSERT INTO PCB01.WARD (WARDNO, WARDNAME) 
VALUES ('01', 'EMGY') 
WHERE HOSPITAL.HOSPCODE = 'H5140070000H'

Inserting a record at a non-root level without specifying foreign key fields

In this statement, the WARD_WARDNO foreign key field is missing. The query will fail because it violates the referential integrity constraint that all foreign keys must be provided with legal values.

INSERT INTO PCB01.PATIENT (HOSPITAL_HOSPCODE, PATNAME, PATNUM)
VALUES ('HW3201', 'JOHN O''CONNER', 'Z800')