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

INSERT Statement—Insert Table Rows

Issue the INSERT statement to insert new rows into a table. You can define as many rows per table as you need.

Keep this information in mind when using 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. As long as the order of columnlist and valuelist match each other, the values will be inserted into the columns properly.
• Unless you choose to specify the QUERY STATEMENT operand, you should provide values for all columns, which are defined in the table as NOT NULL. Otherwise, CA OPS/MVS gives the columns for which you specified no values the default value given on the CREATE TABLE statement defining this table. If you specified 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]

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

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]

You may specify these operands for the INSERT statement:

tablename

Defines the table to which you are adding rows.

columnlist

(Optional) Defines the column names that store the values specified 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 value can be any of the following:

• A date
• A time
• A time stamp
• 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 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.

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. You may specify a value between 1 and 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. Specify OUTPUT to have output returned; 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.

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')