>>--EXEC SQL--.-------------------.---------->
+-FOR :host_integer-+
>---.------------.--INSERT--.------.---.-table_name-.--->
+-AT db_name-+ +-INTO-+ +-view_name--+
+-------- , ------+
V |
>---.---------------.--VALUES (constant_expression)----->
+-(column_list)-+
>------END-EXEC---><
| :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. You can also declare the host variable as PIC S9(4) COMP-3 or PIC S9(9) COMP-3.[1] |
| AT db_name | The name of a database that has been declared using DECLARE DATABASE. This clause is optional. If omitted, the current connection executes the insert. If provided, and the connection specified is different than the current connection, the insert is performed on the connection associated with the DECLARE CURSOR statement. |
| 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. |
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
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.