When the record does not exist:

When using a flattened version of the table, you must specify all of the data to be inserted for the entire record using a single SQL statement. This process is straightforward, as each occurrence of a MU or PE field is a separate column.

When using the rotated and root version of the table, several separate insert statements may be required to insert a new record into ADABAS.

First, you must insert a record into the Root version of the table.

Then, using the ISN returned from the Root insert, you insert data into the appropriate rotated tables.

 

Example:

Insert into employees_root (personnel_id, first_name, last_name) values ('12345678', 'John', 'Smith')

 

Use the following SQL statement to determine the ISN of the last record inserted into ADABAS by CONNX:

 

Select @@IDENTITY

 

Let's say for this example, the above query returns 1004, indicating that the ISN number for the record just inserted is 1004.

Using this ISN number, you can now insert data into the MU and PE fields for this record.

 

Insert into employees_lang (ISN_12_11, LANG) values (1004, 'ENG')

Insert into employees_lang (ISN_12_11, LANG) values (1004, 'FRE')

Insert into employees_lang (ISN_12_11, LANG) values (1004, 'SPA')

 

The above 3 SQL statements add 3 occurrences to the LANG multi-value field for the ADABAS record with ISN # 1004.

 

When the record already exists:

When using flattened version of the table, the only way to add more occurrences to a given record is to use a SQL update statement. Using the update statement, simply provide a value for the column occurrences you want to add. For example, if a record in the employees table currently has a single occurrence in the LANG MU field, and you want to add a second occurrence, use the following SQL statement (you must know the ISN number of the existing record you want to modify):

 

update employees set LANG_2 = 'FRE' where ISN_12_11 = 1004

 

When using the rotated and root version of the table, a SQL insert statement is used to add an occurrence to an existing ADABAS record. Using the same example as above with the rotated version of the tables, the SQL statement would look like this:

insert into employees_lang (ISN_12_11, LANG) values (1004, 'FRE')

 

NOTE: Inserting a value into the SQL count fields that return the # of occurrences in a MU/PE will not cause an error. However, the value will be ignored. The # of occurrences will be strictly be determined by the amount of data inserted into the MU/PE group.