INSERT

Statements › INSERT

INSERT

The INSERT data manipulation statement adds one or more rows to a table.

Authorization

To issue an INSERT statement, you must:

Hold the INSERT privilege on or own the table, view, or table procedure named in the INTO parameter
Hold the SELECT privilege on or own each table, function, view, and table procedure explicitly named in any query specification used in the INSERT statement

Additional authorization requirements apply to:

A view named in the INTO parameter; each view named in the FROM parameter of such a view; each view named in the FROM parameters of those views, and so forth.
For any such view, the owner of the view must own or have the grantable INSERT privilege on each table, view, and table procedure explicitly named in the view definition.
Each view explicitly named in a query specification in the INSERT statement; each view explicitly named in the definition of such a view; each view explicitly named in the definition of those views, and so forth.
For any such view, the owner of the view must own or have the grantable SELECT privilege on each table, view, and table procedure explicitly named in the view definition.

Syntax

►►─── INSERT INTO table-name ─┬─────────────────────────────┬─────────────────►
                              │     ┌────── , ────────┐     │
                              └─ ( ─▼── column-name ──┴─ ) ─┘
               ┌──────────── , ────────────┐
►─┬─ VALUES ( ─▼─┬─ literal ─────────────┬─┴─ ) ────────────────────────────┬─►◄
  │              ├─ host-variable ───────┤                                  │
  │              ├─ local-variable ──────┤                                  │
  │              ├─ routine-parameter ───┤                                  │
  │              ├─ special-register ────┤                                  │
  │              └─ NULL ────────────────┘                                  │
  ├─ query-specification ───────────────────────────────────────────────────┤
  └─ BULK :bulk-buffer ─┬────────────────┬──────────────────────────────────┘
                        └─ bulk-options ─┘

Expansion of bulk-options

►►──┬──────────────────────────────┬──────────────────────────────────────────►
    └─ START :start-variable-name ─┘

 ►──┬─────────────────────────────────┬───────────────────────────────────────►◄
    └─ ROWS :row-count-variable-name ─┘

Parameters

INTO table-name

Identifies the table, view, or table procedure to which new rows are being added. Table-name must not specify a procedure. If table-name identifies a view:

The view must be updateable
The applicable rows are inserted into the table from which the view is derived

For expanded table-name syntax, see Expansion of Table-name.

(column-name)

Specifies one or more columns for which values are being supplied. Column-name must identify a column in the table, view, or table procedure named in the INTO parameter and must be unique within the list of columns.

The list of column names must be enclosed in parentheses. Multiple column names must be separated by commas.

If you specify one or more but not all the columns in the named table, view, or table procedure, CA IDMS stores default or null values in the unspecified columns. If any unspecified column is defined as NOT NULL and does not have a default value, CA IDMS returns an error.

If you do not specify any column names, you must supply values for all columns in the rows being added in the order in which the columns were specified in the table, view, or table procedure definition.

VALUES

Indicates a single row with the specified values is to be added to the table, view, or table procedure named in the INTO parameter. You must provide the same number of values as the number of columns named in the INSERT statement or, if no columns are named, the number of columns in the table, view, or table procedure. The first value specified is stored in the first column named, the second value in the second column, and so on.

The list of values must be enclosed in parentheses. Multiple values must be separated by commas.

Important! You can specify limited types of value expressions within a VALUES clause. For example, you cannot specify scalar functions or expressions with multiple terms.

NULL

Directs CA IDMS to store a null value in the corresponding column in the new row. The column must be defined to allow null values.

query-specification

Specifies a result table whose rows are to be added to the table, view, or table procedure named in the INTO parameter. The specified result table must have the same number of columns as the number of columns named in the INSERT statement or, if no columns are named, the number of columns in the table, view, or table procedure.

For expanded query-specification syntax, see Expansion of Query-specification.

BULK :bulk-buffer

Identifies a variable defined as an array from which CA IDMS is to retrieve the values to be stored in one or more new rows.

Bulk-buffer must be a variable previously declared in the host-language application program or SQL routine. Bulk-buffer must have a subordinate structure which occurs multiple times and has the same number of sub-elements as the number of columns named in the INSERT statement or, if no columns are named, the number of columns in the table, view, or table procedure named in the INTO parameter.

You can specify the BULK parameter only when you embed the INSERT statement in an application program.

The BULK parameter is a CA IDMS extension of the SQL standard.

Parameters for Expansion of bulk-options

START :start-variable-name

Identifies a variable containing the relative position within the bulk buffer from which CA IDMS is to retrieve values for the first new row. Values in subsequent entries in the bulk buffer are retrieved sequentially for subsequent new rows.

Start-variable-name must be a variable previously declared in the host-language application program or SQL routine. The value in the host variable must be an integer in the natural range of subscripts for arrays in the language in which the application program is written.

If you do not specify the START parameter, CA IDMS retrieves the values from the first entry in the bulk buffer.

ROWS :row-count-variable-name

Identifies a variable that specifies the number of rows CA IDMS is to retrieve from the bulk buffer.

Row-count-variable-name must be a variable previously declared in the host-language application program or SQL routine. The value in the host variable must be an integer in the range 1 through the number of rows that fit in the bulk buffer.

If you do not specify the ROWS parameter, CA IDMS retrieves rows from the array sequentially until reaching the end of the buffer.

Note: The bulk-buffer, start-variable-name, row-count-variable-name variables can be host variables, or when the statement is used in an SQL routine, local variables or routine parameters. In this case, their names must not be preceded with a colon.

Usage

Restriction on table-name

The table, view, or table procedure named in the INTO parameter of an INSERT statement cannot also be named in the FROM parameter of a query specification in the same statement or in the FROM parameter of any subquery within the query specification. This means that you cannot INSERT into a table from which you are selecting directly or through a view.

Restriction for Tables in Constraints

If the table named in an INSERT statement is the referencing table in a constraint, the foreign-key columns in each row being inserted must satisfy either of the following conditions:

The columns must be all or partially null
The foreign-key-column values must match the referenced-column values in a row of the referenced table

Compatible Data Types

The data types of the columns named in the INSERT statement and their corresponding values represented by the VALUES, query-specification, or BULK parameter must be compatible for assignment.

Indicator Variables in the INSERT Statement

In an INSERT statement, you can use indicator variables with host variables and within arrays for bulk processing. A negative value in an indicator variable directs CA IDMS to store a null value in the column corresponding to the associated host variable or structure element. CA IDMS ignores an indicator value of 0 or higher.

Errors During Bulk Inserts

If an error occurs during a bulk insert, all rows inserted before the error occurred remain in the table. Subsequent rows, however, are not inserted into the table.

Satisfying Check Constraints

If a row to be inserted into a table does not satisfy the check constraints, if any, in the table definition, CA IDMS returns an error and does not insert the row.

Inserting into Views Having WITH CHECK OPTION

If the INTO parameter includes a view defined with WITH CHECK OPTION, any WHERE clause in the view definition, or in the definitions of any other views nested within its definition, is applied like a check constraint.

Examples

Supplying Explicit Values

The following INSERT statement adds a new row to the PROJECT table. Values (some null, some non-null) are provided for all columns in the table.

insert into project
   values ('P634', 'TV ads - WTVK', '1989-12-01',
      '1990-2-28', null, null, 320, null, 3411);

Using the Values in a Result Table

The following INSERT statement adds new rows to the temporary table TEMP_BUDGET. The values in the new rows come from the result table defined by the query specification in the INSERT statement. Values are provided only for two columns of the temporary table.

insert into temp_budget
   (dept_id, all_expenses)
   select dept_id, adv_exp + merch_exp + op_exp + misc_exp
      from proposed_budget;

Inserting Values from a Buffer

The following INSERT statement adds new rows to the CONSULTANT table. Values for the new rows come from the buffer CNSLT-BUFF. The number of rows added is determined by the value in the host variable BUFF-ROW-COUNT.

EXEC SQL
INSERT INTO CONSULTANT
   BULK :CNSLT-BUFF
   ROWS :BUFF-ROW-CNT
END-EXEC

More Information

For more information about updateable views, see CREATE VIEW.
For more information about host variables, see Host Variables.
For more information about expanded host variable, local variable, or routine parameters syntax, see Expansion of Host-variable, Local Variables, or Routine Parameters.
For more information about compatible data types for assignment operations, see Comparison, Assignment, Arithmetic, and Concatenation Operations.
For more information about null values, see Null Values.
For more information about bulk processing in an application program, see the CA IDMS SQL Programming Guide.
For more information about expanded literal syntax, see Expansion of Literal.
For more information about expanded special-register syntax, see Expansion of Special-register.