INSERT

The INSERT statement inserts rows into a table.

The INSERT via VALUES form is used to insert a single row into the table using the values provided or referenced.

Invocation

This statement can be embedded in a COBOL or Java application program or issued interactively. An INSERT can be embedded in an application program. It is an executable statement that can be dynamically prepared.

Syntax for COBOL

Read syntax diagramSkip visual syntax diagram INSERT INTO table (,column)VALUES(,value)

table syntax

Read syntax diagramSkip visual syntax diagramschema-name.table-name

column syntax

Read syntax diagramSkip visual syntax diagramtable-name. column-name

multi-row-insert syntax

Read syntax diagramSkip visual syntax diagramtable-name. VALUES expressionhost-variable-arrayNULLDEFAULT(,expressionhost-variable-arrayNULLDEFAULT)

Syntax for Java

Read syntax diagramSkip visual syntax diagram INSERT INTO table (,column)VALUES(,value)
table
Read syntax diagramSkip visual syntax diagramschema-name.table-name
column
Read syntax diagramSkip visual syntax diagramschema-name.table-name.column-name1
Notes:
  • 1 You can have the same column name in multiple tables, but if the table is not qualified, each table must be searched for the column.

Description

The following keyword parameters are defined for the INSERT statement:
INSERT INTO
Identifies the object of the INSERT statement.
table-name
The table-name defines the name of the table in your SQL query. The name must identify a segment in IMS.
schema-name
The schema-name defines the schema in your SQL query. In IMS, the schema name is the PCB name.
column-name
Specifies the columns for which insert values are provided. Each name must identify a field of the segment. The columns can be identified in any order, but the same column must not be identified more than one time.

Omission of the column list is an implicit specification of a list in which every column in the table is identified in the order identified by the metadata.

VALUES
Specifies one new row in the form of a list of values. The number of values in the VALUES clause must be equal to the number of names in the column list. The first value is inserted in the first column in the list, the second value in the second column, and so on. The list of values must be enclosed in parentheses.

Notes

Insert rules:
Insert values must satisfy the following rules. If they do not, or if any other errors occur during the execution of the INSERT statement, no rows are inserted and the position of the cursors are not changed.
  • Length. If the insert value of a column is a number, the column must be a numeric column with the capacity to represent the integral part of the number. If the insert value of a column is a string, the column must be either a string column with a length attribute at least as great as the length of the string, or a datetime column if the string represents a date, time, or timestamp.
  • Referential constraints. 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.
  • Omitting the column list. When you omit the column list, you must specify a value for every column that was present in the table when the INSERT statement was bound or (for dynamic execution) prepared.
Number of rows inserted:
For COBOL, the value of SQLIMSERRD(3) in the SQLIMSCA is the number of rows inserted after an INSERT statement completes execution. For a complete description of the SQLIMSCA, see SQL communication area (SQLIMSCA).
Inserting binary fields:
For COBOL, when inserting a binary field, you must use a parameter marker to specify the binary value. Not using a parameter marker would result in a 408 (data type not compatible) error.

Examples

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 virtual 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 virtual 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 virtual 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.
Inserting a record at a non-root level without specifying virtual foreign key fields
In this statement, the WARD_WARDNO virtual 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')