The INSERT data manipulation statement adds one or more rows to a table.
The INSERT data manipulation statement adds one or more rows to a table.
This article describes the following information:
In the VALUES clause, you can include expressions that include operators, built-in and user-defined functions to determine the virtual foreign key values that are used in the row insertion.
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.
►►─── INSERT INTOtable-name─┬─────────────────────────────┬─────────────────► │ ┌────── , ────────┐ │ └─ ( ─▼── column-name ──┴─ ) ─┘ ┌──────────── , ────────────┐ ►─┬─ VALUES ( ─▼─┬─value-expression ────┬─┴─ ) ────────────────────────────┬─►◄ │ ├─(query-expression)────┤ │ │ └─ NULL ────────────────┘ │├─ query-specification───────────────────────────────────────────────────┤ └─BULK :bulk-buffer─┬────────────────┬──────────────────────────────────┘└─ bulk-options─┘
Expansion of bulk-options
►►──┬──────────────────────────────┬──────────────────────────────────────────► └─ START :start-variable-name─┘ ►──┬─────────────────────────────────┬───────────────────────────────────────►◄ └─ ROWS :row-count-variable-name─┘
- INTO table-nameIdentifies the table, view, or table procedure to which new rows are being added. Table-name must not specify a procedure. Iftable-nameidentifies a view:
table-namesyntax, see Expansion of Table-name.
- The view must be updateable
- The applicable rows are inserted into the table from which the view is derived
- (column-name)Specifies one or more columns for which values are being supplied.Column-namemust 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.For schemas with virtual keys, the virtual keys follow the columns in the definition and are in the order of ROWID followed by the virtual foreign keys in alphabetical order.
- VALUESIndicates 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. If you specify one or more but not all of the columns in the named table, view, or table procedure, CA IDMS stores default or null values in the unspecified columns.The list of values must be enclosed in parentheses. Multiple values must be separated by commas.
- NULLDirects CA IDMS to store a null value in the corresponding column in the new row. The column must be defined to allow null values.
- value-expressionRepresents a value used for the corresponding column in the new row. For expandedvalue-expressionsyntax, see Expansion of Value-expression.You can specify limited types of value expressions within a VALUES clause. You cannot specify aggregate functions or column names, including the ROWID pseudo-column.
- query-expressionRepresents a value used for the corresponding column in the new row. The query-expression must return at most, one row and the result table of the query-expression must consist of a single column. For expandedquery-expressionsyntax see Expansion of Query-expression.
- query-specificationSpecifies 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 expandedquery-specificationsyntax, see Expansion of Query-specification.
- BULK :bulk-bufferIdentifies 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-buffermust be a variable previously declared in the host-language application program or SQL routine.Bulk-buffermust 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-nameIdentifies 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-namemust 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-nameIdentifies a variable that specifies the number of rows CA IDMS is to retrieve from the bulk buffer.Row-count-variable-namemust 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.
row-count-variable-namevariables 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.
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.
Using a query-expression as a Source Value
If a query-expression, used as the value stored in a column, returns no rows, the column is set to the null value. If the column does not allow nulls, an exception is raised.
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 SQLINSERT INTO CONSULTANT BULK :CNSLT-BUFF ROWS :BUFF-ROW-CNTEND-EXEC
Using query-expressions to INSERT columns
The following INSERT statement sets the value of the SALARY_BUDGET column in the DEPARTMENT table, based on the current salaries of all employees in the department.
Insert into department d (dept_id, salary_budget) values (3200, (select 1.1 * sum (salary) from employee e where e.deptid = d.deptid));
Inserting into a table of a schema with virtual keys
The following INSERT statement sets the values of the columns including setting values for ROWID and virtual foreign keys.
INSERT INTO EMPLOYEE (EMP_ID_0415, EMP_FIRST_NAME_0415, EMP_LAST_NAME_0415, ROWID, FKEY_DEPT_EMPLOYEE, FKEY_OFFICE_EMPLOYEE)VALUES (911, 'Kathryn’, 'Smith', NULL, (SELECT ROWID FROM DEPARTMENT WHERE DEPT_ID_0410 = 9002), (SELECT ROWID FROM OFFICE WHERE OFFICE_CODE_0450 = '002'));
- For more information about updateable views, see CREATE VIEW.
- For more information about host variables, see Host Variables.
- 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 Bulk Processing.
- For more information about expanded literal syntax, see Expansion of Literal.
- For more information about expanded special-register syntax, see Expansion of Special-register.