Inserting Rows

To insert rows into a table or view, use the INSERT statement. The values you assign to columns during the insert can be literal values or values which have been placed in host variables.

Literal values assigned to columns that contain character data must be enclosed in apostrophes ('). Literal values assigned to columns of numeric data types are not enclosed in apostrophes.

The data type of the host variable must be compatible with the data type of the column to which the value is being assigned.

If you specify the column names in the INSERT statement, the values for the columns must be listed in the same order as the column names. This is shown in Example 1.

If you do not specify the column names in the INSERT statement, the values for the columns must be listed in the same order as the columns are specified in the base table (see Example 2).

Example 1


 Problem

Add rows to the CUSTOMERS table where the value of CUST_NO and NAME
are assigned from the host variables WC-CUSTNO and WC-NAME.
 Solution

               .
               .
       (COBOL statements)
               .
               .
       (loop until end of input)
               .
               .
  1   EXEC SQL
  2       INSERT INTO CUSTOMERS (CUST_NO, NAME)
  3       VALUES (:WC-CUSTNO, :WC-NAME)
  4   END-EXEC.

Lines 2-3

The row is inserted in the CUSTOMERS table with the values in the host variables assigned to the columns CUST_NO and NAME.

Example 2

The following problem shows how to insert a row in a table without having to specify column names to assign a value to each column.


 Problem

Add a row to the CUSTOMERS table with values specified for each
column.  The values must be specified in the same order as the columns
are listed in the table definition.
 Solution

               .
               .
       (COBOL statements)
               .
               .
  1   EXEC SQL
  2       INSERT INTO CUSTOMERS
  3       VALUES ('Z',
  4               '9999',
  5               'LAGOONS R US',
  6               '925 DAILY DRIVE',
  7               'BOX 25',
  8               'AGANA',
  9               'GU',
 10               '89333',
 11               'B',
 12               '808',
 13               '967',
 14               '2774')
 15   END-EXEC.

Lines 2-14

The row is inserted into the CUSTOMERS table with the specified value for each column. Since the values are listed in the same order that the columns are specified in the table definition, it is not necessary to name the columns.