Previous Topic Next topic Print topic


INSERT

Adds new rows to a table.

Syntax:

>>--EXEC SQL--.-------------------.---------->
              +-FOR :host_integer-+  

 >---.------------.--INSERT--.------.---.-table_name-.--->
     +-AT db_name-+          +-INTO-+   +-view_name--+

                                +-------- , ------+  
                                V                 |
 >---.---------------.--VALUES (constant_expression)----->
     +-(column_list)-+
 
 >------END-EXEC---><

Parameters:

:host_integer A host variable that specifies the maximum number of host array elements processed. Must be declared as PIC S9(4) COMP-5 or PIC S9(9) COMP-5.
db_name The name of a database that has been declared using DECLARE DATABASE.
table_name The table into which rows are to be inserted.
view_name The view into which rows are to be inserted.
INTO An optional keyword. Required for ANSI SQL 92 conformance.
column_list A list of one or more columns to which data is to be added. The columns can be in any order, but the incoming data must be in the same order as the columns. The column list is necessary only when some, but not all, columns in the table are to receive data. Enclose the items in the column list in parentheses. If no column list is given, all the columns in the receiving table (in CREATE TABLE order) are assumed.

The column list determines the order in which values are entered.

VALUES Introduces a list of constant expressions.
constant_expression Constant or null values for the indicated columns. The values list must be enclosed in parentheses and must match the explicit or implicit columns list. Enclose non-numeric constants in single or quotation marks.

Comments:

The INSERT statement is passed directly to the ODBC driver. See the documentation supplied with your ODBC driver for the exact syntax.

If the host variables in the WHERE clause are arrays, the INSERT statement is executed once for each set of array elements.

Uuse UPDATE to modify column values in an existing row.

You can omit items in the column list and VALUES list providing that the omitted columns are defined to allow null values.

You can select rows from a table and insert them into the same table in a single statement.

After execution, SQLERRD(3) contains the number of elements processed. For INSERT it is the total number of rows inserted.

Example:

     DISPLAY "Enter new staff member's id:"
     ACCEPT staff-id

     DISPLAY "Enter new staff member's last name:"
     ACCEPT last-name

     DISPLAY "Enter new staff member's first name:"
     ACCEPT first-name

     DISPLAY "Enter new staff member's age:"
     ACCEPT age

     DISPLAY "Enter new staff member's employment date(yyyy/mm/dd):"
     ACCEPT employment-date

     EXEC SQL
        INSERT INTO staff
        (staff_id
       ,last_name
       ,first_name
      ,age
       ,employment_date)
        VALUES
        (:staff-id
       ,:last-name
       ,:first-name
       ,:age
       ,:employment-date)
     END-EXEC
Previous Topic Next topic Print topic