Data Manipulation with SQL › Data Manipulation Operations › Adding Data

Adding Data

Using INSERT

In a program, you use an INSERT statement to add data to the database in one of the following ways:

• INSERT with a VALUES clause to add a single row to a table by listing the column values in the statement
• INSERT with a SELECT statement to add one or more rows using existing data
• INSERT with a BULK clause to add multiple rows to a table from a host variable array

Single-row INSERT

To add a single row to a table, issue an INSERT statement with a VALUES clause that specifies a value for each column in the column list:

EXEC SQL
  INSERT INTO DIVISION
              (DIV_CODE, DIV_NAME)        ◄─── Column list
     VALUES (:DIV-CODE, :DIV-NAME)
END-EXEC.

Multiple-row INSERT with SELECT

One way to add multiple rows to a table is to insert the result table of a SELECT statement.

In this example, a result table of data from the EMPLOYEE table is inserted into a table named TEMP_MGR:

EXEC SQL
  INSERT INTO TEMP_MGR
  SELECT DISTINCT E.MANAGER_ID,
         M.EMP_FNAME,
         M.EMP_LNAME
    FROM EMPLOYEE E, EMPLOYEE M
    WHERE E.MANAGER_ID = M.EMP_ID
END-EXEC.

Guidelines for INSERT

Apply these guidelines when formulating an INSERT statement:

• An INSERT statement must supply a value for each column in the column list, even if the value is null
• The order of column values must match the order of the column list
• An INSERT statement must supply values for all columns of the named table if the column list is omitted:

  EXEC SQL
    INSERT INTO DIVISION
       VALUES ('D06', 'ADVANCED RESEARCH', NULL)
              -- Division head id is null --
  END-EXEC.
  

  When embedding an INSERT statement with a VALUES clause, you should include a column list even if you mean to insert values into all columns. Using a VALUES clause but omitting a column list can cause a program error if, for example, a column has been added to the table.

• A column list must include any table columns that are defined as not null and as not having a default value

  If an INSERT statement omits a table column from the column list, the DBMS:

  • Stores the default value for the column, if one has been defined
  • Stores a null value if the column allows nulls
  • Returns a data exception error if no default value has been defined and nulls are not allowed

Checking INSERT Status

Since the DBMS enforces integrity constraints, the program can test SQLCERC for a constraint violation:

• 1023—Check constraint
• 1058—Unique constraint
• 1060—Referential constraint
• 1002—Null constraint
• 1031—Page group violation

  Note: Referential constraints defined as linked clustered are not permitted to not cross page group boundaries.

Here is an example for a specific test for a check constraint violation:

IF SQLCERC = 1023 PERFORM INVALID-DATA
ELSE IF SQLCODE < 0 GOTO ERROR-ROUTINE.

If an INSERT statement that uses a SELECT statement executes successfully but adds no rows, the DBMS returns 100 to SQLCODE and 0 to SQLCNRP.

Inserting Multiple Rows

You can add a set of rows to a table using one INSERT statement with a BULK clause.

Note: For more information about using bulk processing to insert, see Bulk Processing.