Relational Data Framework Reference › SQL Statements › INSERT Statement—Insert Table Rows

INSERT Statement—Insert Table Rows

The INSERT statement lets you insert new rows into a table. You can define as many rows per table as you need.

Consider the following information when you use the INSERT statement:

• You can use host variables for columnlist or tablename.
• The order of the columns in columnlist must match the order of the values in valuelist. You do not need to match the actual order of the columns in the table with columnlist. If the order of columnlist and valuelist match each other, the values are inserted into the columns properly.
• Unless you specify the QUERY STATEMENT operand, provide values for all columns that are defined in the table as NOT NULL. Otherwise, CA OPS/MVS gives the columns for which you specify no values the default value on the CREATE TABLE statement that defines this table. If you specify no default, the CA OPS/MVS product makes the columns null.

Use this syntax in an AOF rule or an OPS/REXX program:

ADDRESS SQL "INSERT keywords"

{INTO}
[(columnlist)]
/* The following keywords are mutually exclusive:  */
[VALUES(valuelist)]
[QUERY STATEMENT]
/* Place these keywords at the end of the command if you use them: */
[SYSTEM(ALL|EXT|sysnames)]
[SYSWAIT(seconds)]
[OUTPUT|NOOUTPUT]
[SYSPLEX]
[SUBSYS(subsystem)]

To invoke the statement from a TSO terminal, a TSO/E REXX program, or a TSO CLIST, use this syntax:

OPSQL INSERT

/* Specify these required keywords in the order shown:   */
{INTO}
{tablename}
/* Optional keywords      */
[(columnlist)]
/* The following keywords are mutually exclusive:   */
[VALUES(valuelist)]
[QUERY STATEMENT]

Specify these operands for the INSERT statement:

tablename

Defines the table to which you add rows.

columnlist

(Optional) Defines the column names that store the values that you specify with the valuelist operand. If you specify no columnlist, SQL stores the values into the columns in the order in which they were defined on the CREATE TABLE statement for this table.

valuelist

(Optional) Defines the column values for the new row. A column can have any of the following values:

• A date
• A time
• A timestamp
• A character string
• A numeric string
• A host variable
• NULL (which sets a null value)

QUERY STATEMENT

(Optional) Retrieves the values to be inserted into the table. This value can be any valid search criteria. See the chapter “Using the Relational Data Framework” in the User Guide.

SYSTEM

(Optional) Performs cross-system SQL operations. Specify one of these values:

ALL

Routes the SQL command to all active MSF-defined systems, including the local system.

EXT

Routes the SQL command to all remote, active MSF-defined systems.

sysnames

Routes the SQL command to the specified systems. You may specify from one to eight system names as the value of sysnames.

Note: For more information, see the chapter “Using the Relational Data Framework” in the User Guide.

SYSWAIT

(Optional) Defines the number of seconds the SQL processor waits for output from a remote system.

Limits: 1-300 seconds

Do not specify a value for SYSWAIT if you specify the SYSTEM(ALL), SYSTEM(EXT), or NOOUTPUT keywords.

OUTPUT or NOOUTPUT

(Optional) Indicates whether the command returns output to the external data queue. To return output, specify OUTPUT; otherwise, specify NOOUTPUT.

NOOUTPUT is implied when:

• The ADDRESS SQL command is issued in an AOF rule.
• You specify more than one system name as the value of the SYSTEM(sysnames) keyword.
• You specify SYSTEM(ALL) or SYSTEM(EXT).

SYSPLEX

(Optional) Reduces the scope of the SYSTEM(ALL|EXT) operand to MSF connected systems that belong to the same z/OS sysplex as the command issuer. The keyword has no effect on a list of explicit system names.

SUBSYS

(Optional) Performs SQL operations on other CA OPS/MVS subsystems that are in communication with the current one. Specify the following value:

subsystem

Routes the SQL command to the specified subsystem. You can use only one four-character name of the subsystem as follows: OPSx.

Examples: Using the INSERT statement

These examples illustrate the use of the INSERT statement:

• Example 1-Inserting Values into Selected Columns

  When your table contains many columns but you need to set values for only a few columns initially, you can insert values into selected columns. When specifying column values with the VALUES clause, specify them in the same order you used to specify columns in the column list. The following sample statement, invoked by OPS/REXX, inserts a row into the SYSTEMS table. This row will have the values CICS1 and DOWN in the NAME and CURRENT_STATE columns respectively:

  ADDRESS SQL
   "INSERT INTO SYSTEMS (NAME, CURRENT_STATE) VALUES ('CICS1' 'DOWN')"
  

• Example 2-Specifying Values Through Host Variables

  You also can specify values through host variables. For example, to insert a job name value into a row of the SYSTEMS table, you could use the variable JOBNAME in an AOF rule as follows:

  JOBNAME = MSG.JOBNAME
  ADDRESS SQL
    "INSERT INTO SYSTEMS (NAME) VALUES (:JOBNAME)"
  

• Example 3-Using a Query Statement

  Suppose that you have two tables. The APPLICATIONS table contains four columns: APPL_ID, USER_ID, UPDATE, and STATUS. The structure of the NEWAPPS table is identical to that of the APPLICATIONS table. You might want to add a row to the APPLICATIONS table for each of the new applications that have been altered by a certain three users. In this case, you could substitute the VALUES clause with a query statement and invoke your INSERT statement from a TSO CLIST as follows:

  OPSQL INSERT INTO APPLICATIONS SELECT * FROM NEWAPPS +
    WHERE USER_ID IN ('TSOUSR1','TSOUSR2','TSOUSR3')