6.3.2 INSERT Command

The SQL INSERT command is used to load one or more rows into an existing table, thereby updating the data in your COBOL data files.

Syntax:

INSERT INTO <table-name> [(<field-list>)]
      VALUES (<values-list>)

The INSERT command can include subqueries, as in the following example:

INSERT INTO articles1
SELECT *
FROM articles2
WHERE ar_code < 5;

<table-name>

is the name of the table where records must be inserted.

<field-list>

is an optional list of fields to which you want to assign a value. This list must be written according to the following syntax:

<field-name> [,[<field-name>] [, ...]]

Omitting this list specifies all the fields of the table, using the same order as they are defined.

<value-list>

is a list of literals. The value of these literals is assigned to the fields specified in <field-list>, according to the order in which they are listed. For this reason the number of elements of both the lists must be equal. At the same time, the type of field (alphabetic, numeric, etc.) must be compliant with the value of the literal.

<value-list> has the following format:

<literal> [,[<literal>] [, ...]]

Example:

INSERT INTO CLIENTS (COMPANY-NAME, ADDRESS, ZIP-CODE)
 VALUES ("Acucorp Inc.", "8515 Miralani Drive", 92126).

This command creates a new row in the table named CLIENTS, and assigns the value "Acucorp Inc" to the field COMPANY-NAME, the value "8515 Miralani Drive" to ADDRESS, and 92126 to ZIP-CODE. As required by the syntax, the number of fields (three) corresponds exactly to the number of values.

The following examples are incorrect.

INSERT INTO CLIENTS (COMPANY-NAME, ADDRESS, ZIP-CODE)
 VALUES ("Acucorp Inc.", 92126, "8515 Miralani Drive",).

The positions of values don't match with the positions of the fields. 8515 Miralani Drive is an alphanumeric value, while ZIP-CODE is numeric. An error message would result.

INSERT INTO CLIENTS (COMPANY-NAME, ADDRESS, ZIP-CODE)
 VALUES ("Acucorp Inc.", "8515 Miralani Drive").

In this example, the number of values does not correspond to the number of fields. An error message would result.