Skip to main content

INSERT

Adds a new row (or rows) to a table.

Synopsis

INSERT [%NOFPLAN] [restriction] [INTO] table
          SET column1 = scalar-expression1 
                 {,column2 = scalar-expression2} ...  |
          [ (column1{,column2} ...) ] 
                 VALUES (scalar-expression1 {,scalar-expression2} ...)  |
          VALUES :array()  |
          [ (column1{,column2} ...) ] query  |
          DEFAULT VALUES

Arguments

Argument Description
%NOFPLAN Optional — The %NOFPLAN keyword specifies that Caché will ignore the frozen plan (if any) for this operation and generate a new query plan. The frozen plan is retained, but not used. For further details, refer to Frozen Plans in Caché SQL Optimization Guide.
restriction Optional — One or more of the following restriction keywords, separated by spaces: %NOLOCK, %NOCHECK, %NOINDEX, %NOTRIGGER.
table The name of the table or view on which to perform the insert operation. This argument may be a subquery. The INTO keyword is optional. A table name (or view name) can be qualified (schema.table), or unqualified (table). An unqualified name is matched to its schema using either a schema search path (if provided) or the system-wide default schema name.
column Optional — A column name or comma-separated list of column names that correspond in sequence to the supplied list of values. If omitted, the list of values is applied to all columns in column-number order.
scalar-expression A scalar expression or comma-separated list of scalar expressions that supplies the data values for the corresponding column fields.
:array() Embedded SQL only — A dynamic local array of values specified as a host variable. The lowest subscript level of the array must be unspecified. Thus :myupdates(), :myupdates(5,), and :myupdates(1,1,) are all valid specifications.
query A SELECT query the result set of which supplies the data values for the corresponding column fields for one or multiple new rows.

Description

The INSERT statement can be used in two ways:

  • A single-row INSERT adds one new row to a table. It inserts data values for all specified columns (fields) and defaults unspecified column values to either NULL or the defined default value. It sets the %ROWCOUNT variable to the number of affected rows (always either 1 or 0).

  • An INSERT with a SELECT adds multiple new rows to a table. It inserts data values for all specified columns (fields) for each row from the query result set and defaults unspecified column values to either NULL or the defined default value. This use of an INSERT statement combined with a SELECT query is commonly used to populate a table with existing data extracted from other tables, as described in the “INSERT Query Results” section below.

This reference page is structured as follows:

INSERT OR UPDATE

The INSERT OR UPDATE statement is a variant of the INSERT statement that performs both insert and update operations. First it attempts to perform an insert operation. If the insert request fails due to a UNIQUE KEY violation (for the field(s) of some unique key, there exists a row that already has the same value(s) as the row specified for the insert), then it automatically turns into an update request for that row, and INSERT OR UPDATE uses the specified field values to update the existing row.

Restriction Keywords Argument

Specifying restriction argument(s) restricts processing as follows:

  • %NOCHECK — Unique value checking and foreign key referential integrity checking are not performed. Column data validation for data type, maximum length, data constraints, and other validation criteria is also not performed. The WITH CHECK OPTION validation for a view is not performed when performing an INSERT through a view.

    Note:

    Because use of %NOCHECK can result in invalid data, this %keyword argument should only be used when performing bulk inserts or updates from a reliable data source.

    The user must have the corresponding %NOCHECK administrative privilege for the current namespace to apply this restriction. Failing to do so results in an SQLCODE –99 error with the %msg User does not have %NOCHECK privileges.

    If you wish to prevent inserts of non-unique data values when specifying %NOCHECK, perform an EXISTS check prior to INSERT.

    If you wish to disable only foreign key referential integrity checking, use the $SYSTEM.SQL.SetFilerRefIntegrity()Opens in a new tab method rather than specifying %NOCHECK. Alternatively, a foreign key can be defined with the NOCHECK keyword, so that foreign key referential integrity checking is never performed.

  • %NOINDEX — the index maps are not set during INSERT processing. The user must have the corresponding %NOINDEX administrative privilege for the current namespace to apply this restriction. Failing to do so results in an SQLCODE –99 error with the %msg User does not have %NOINDEX privileges.

  • %NOLOCK — the row is not locked upon INSERT. This should only be used when a single user/process is updating the database. The user must have the corresponding %NOLOCK administrative privilege for the current namespace to apply this restriction. Failing to do so results in an SQLCODE –99 error with the %msg User does not have %NOLOCK privileges.

  • %NOTRIGGER — the base table triggers are not pulled during INSERT processing. The user must have the corresponding %NOTRIGGER administrative privilege for the current namespace to apply this restriction. Failing to do so results in an SQLCODE –99 error with the %msg User does not have %NOTRIGGER privileges.

You can specify multiple restriction arguments in any order. Multiple arguments are separated by spaces.

Table Argument

You can specify the table argument to insert into a table directly, insert through a view, or insert via a subquery. Inserting through a view is subject to requirements and restrictions, as described in CREATE VIEW. The following is an example of an INSERT using a subquery in place of the table argument:

INSERT INTO (SELECT field1 AS ff1 FROM MyTable) (ff1) VALUES ('test')

The subquery target must be updateable, following the same criteria used to determine if a view's query is updateable. Attempting to INSERT using a view or a subquery that is not updateable generates an SQLCODE -35 error.

You cannot specify a table-valued function or JOIN syntax in the table argument.

For required table privileges, refer to Privileges. For error codes, refer to SQLCODE Errors.

Value Assignment

This section describes how data values are assigned to columns (fields) during an INSERT operation:

If you omit the column list argument, the INSERT assumes all columns are to be inserted, in column number order. If you specify a column list, the individual values must correspond positionally with the column names in the column list.

Value Assignment Syntax

When inserting a record, you can assign values to specified columns in a variety of ways. All non-specified columns must either accept NULL or have a defined default value.

  • Explicit column names using the SET keyword, specify one or more column = scalar-expression pairs as a comma-separated list. For example:

    SET StatusDate='05/12/06',Status='Purged'
    
  • Explicit column names using the VALUES keyword, specify a list of columns equated to a corresponding scalar-expressions list. For example:

    (StatusDate,Status) VALUES ('05/12/06','Purged')
    

    When assigning scalar-expression values to a column list, there must be a scalar-expression for each specified column.

  • No column names. When using the VALUES keyword without a column list, specify a list of scalar-expressions that correspond to the table’s fields in order. For example:

    VALUES ('Fred Wang',65342,'22 Main St. Anytown MA','123-45-6789')
    

    Values must be specified in column number order. You must specify a value for every base table column that takes a user-supplied value; an INSERT using column order cannot take defined field default values. An SQLCODE -62 error is issued if you specify fewer values than the number of table columns. An SQLCODE -116 error is issued if you specify more values than the number of table columns.

    The RowID column cannot be user specified, and is therefore not included in this syntax.

    By default, a table with a defined IDENTITY field or RowVersion field cannot be populated using this syntax. If one of these fields is defined, this INSERT syntax issues an SQLCODE -62 error if you do not specify a value for these fields, and issues an SQLCODE -138 Cannot INSERT/UPDATE a value for a read only field error if you do specify a value for these fields. (An IDENTITY field can be configured to allow user-supplied values; see IDENTITY field.)

    A table with a defined SERIAL (%Counter) field can be populated using this syntax, but a user-supplied value must be specified for these this counter field.

  • No column names. When using the VALUES keyword without a column list, specify a dynamic local array of scalar-expressions that implicitly correspond to the columns of the row in column order. For example:

    VALUES :myarray()
    

    This value assignment can only be performed from Embedded SQL using a host variable. Unlike all other value assignments, this usage allows you to delay specifying which columns are to be inserted until runtime (by populating the array at runtime). All other types of insert require that you specify which columns are to be inserted at compile time.

    Values must be specified in column number order. You must specify a value for every base table column that takes a user-supplied value; an INSERT using column order cannot take defined field default values. Supplied array values must begin with array(2). Column 1 is the RowID field; you cannot specify a value for the RowID field. For further details, see “Host Variable as a Subscripted Array” in the “Using Embedded SQL” chapter of Using InterSystems SQL.

If you specify column names and corresponding data values, you can omit columns for which there is a defined default value. An INSERT can insert a default value for most field data types, including stream fields.

If you do not specify column names, data values must correspond positionally to the defined column list. You must specify a value for every user-specifiable base table column; defined default values cannot be used. (You can, of course, specify an empty string as a column value.)

To list all of the column names and column numbers defined for a specified table, refer to Column Names and Numbers in the “Defining Tables” chapter of Using Caché SQL.

For required column privileges, refer to Privileges. For error codes, refer to SQLCODE Errors.

Display to Logical Data Conversion

Data is stored in LOGICAL mode format. For example, a date is stored as an integer count of days, a time is stored as a count of seconds from midnight, a %List is stored as an encoded string. Most other data, such as strings and numbers, require no conversion; they are both input and stored in the same format regardless of the current mode.

For data that is converted, you can input data in LOGICAL mode (the default), or input data in a more human-readable format (DISPLAY mode or ODBC mode) by specifying the select mode. For example, by specifying the select mode you can input a date in DISPLAY format (for example, 2/22/2018), ODBC format (for example, 2018-02-22), or LOGICAL format (for example, 64701). For certain data types you can also specify data in LOGICAL format when in ODBC or DISPLAY select mode. You can explicitly set the select mode in SQL execution environments as follows:

Input data that is not in LOGICAL mode format must be converted to LOGICAL mode format for storage. Compiled SQL supports automatic conversion of input values from DISPLAY or ODBC format to LOGICAL format. An input value that cannot be converted results in an SQLCODE error, for example SQLCODE -146 and SQLCODE -147. Automatic conversion of input data requires two factors: when compiled, the SQL must specify RUNTIME mode; when executed, the SQL must execute in a LOGICAL mode environment.

  • In Embedded SQL, if you specify #sqlcompile select=runtime, Caché will compile the SQL statement with code that converts input values from a display format to LOGICAL mode storage format. Caché performs this mode conversion both for single values and for arrays of values. For further details, see #sqlcompile select in the “ObjectScript Macros and the Macro Preprocessor” chapter of Using Caché ObjectScript.

  • In an SQL CREATE FUNCTION, CREATE METHOD, or CREATE PROCEDURE statement, if you specify SELECTMODE RUNTIME, Caché will compile the SQL statement with code that converts input values from a display format to LOGICAL mode storage format.

The data is stored in LOGICAL format if the SQL execution environment is in LOGICAL mode. This is the default mode for all InterSystems SQL execution environments.

%SerialObject Properties

When inserting data into a %SerialObject, you must insert into the table (persistent class) that references the embedded %SerialObject; you cannot insert into a %SerialObject directly. From the referencing table, you can either:

  • Use the referencing field to insert values for multiple %SerialObject properties as a %List structure. For example, if the persistent class has a property PAddress that references a serial object contain the properties Street, City, and Country (in that order), you insert SET PAddress=$LISTBUILD('123 Main St.','Newtown','USA'). The %List must contain values for the properties of the serial object (or placeholder commas) in the order that these properties are specified in the serial object.

  • Use underscore syntax to insert values for individual %SerialObject properties in any order. For example, if the persistent class has a property PAddress that references a serial object contain the properties Street, City, and Country, you insert SET PAddress_City='Newtown',PAddress_Street='123 Main St.',PAddress_Country='USA'. Unspecified serial object properties default to NULL.

Non-Display Characters

You can insert non-display characters using the CHAR function and the concatenation operator. For example, the following example inserts a string consisting of the letter “A”, a line feed, and the letter “B”:

INSERT INTO MyTable (Text) VALUES ('A'||CHAR(10)||'B')

Note that to concatenate the results of a function you must use the || concatenation operator, not the _ concatenation operator.

A query can determine if a non-display character is present using the LENGTH or $LENGTH function.

Special Variables

You can insert into a column the value of the following special variables:

A %TABLENAME, or %CLASSNAME pseudo-field variable keyword. %TABLENAME returns the current table name. %CLASSNAME returns the name of the class corresponding to the current table.

One or more of the following ObjectScript special variables (or their abbreviations): $HOROLOG, $JOB, $NAMESPACE, $TLEVEL, $USERNAME, $ZHOROLOG, $ZJOB, $ZNSPACE, $ZPI, $ZTIMESTAMP, $ZTIMEZONE, $ZVERSION.

Stream Data

You can insert the following types of data values into a stream field:

  • An object reference (OREF) to a stream object. Caché opens this object and copies its contents into the new stream field. For example:

        set oref=##class(%Stream.GlobalCharacter).%New()
        do oref.Write("Technique 1")
    
        //do the insert; use an actual OREF
        &sql(INSERT INTO MyStreamTable (MyStreamField) VALUES (:oref))
    
  • A string version of an OREF of a stream, for example:

        set oref=##class(%Stream.GlobalCharacter).%New()
        do oref.Write("Technique 2")
    
        //next line converts OREF to a string OREF
        set string=oref_""
    
        //do the insert
        &sql(INSERT INTO MyStreamTable (MyStreamField) VALUES (:string))
    
  • A numeric value, such as 1 or -1.

  • A string literal whose first character is not numeric, for example:

        set literal="Technique 3"
    
        //do the insert; use a string
        &sql(INSERT INTO MyStreamTable (MyStreamField) VALUES (:literal))
    

    If the first character is numeric, SQL interprets the literal as the string form of an OREF instead. For example, the value 2@User.MyClass would be considered the string version of an OREF, and not a string literal.

Attempting to insert an improperly defined stream value results in an SQLCODE -412 error: General Stream Error.

For further details, refer to Inserting Data into Stream Data Fields.

List Structured Data

Caché supports the list structure data type %List (data type class %Library.List). This is a compressed binary format, which does not map to a corresponding native data type for Caché SQL. It corresponds to data type VARBINARY with a default MAXLEN of 32749. For this reason, Dynamic SQL cannot use INSERT or UPDATE to set a property value of type %List. For further details, refer to the Data Types reference page in this manual.

Insert Counter Values

A table can optionally have one field defined as IDENTITY. By default, this field receives an integer from an automatically incremented table counter whenever a row is inserted into the table. By default, an insert cannot specify a value for this field. However, this default is configurable. An IDENTITY field value cannot be modified by an update operation. This counter is reset by a TRUNCATE TABLE operation.

A table can optionally have one or more fields defined as data type SERIAL (%Library.CounterOpens in a new tab). By default, this field receives an integer from an automatically incremented table counter whenever a row is inserted into the table. However, a user can specify an integer value for this field during an insert, overriding the table counter default. A %Counter field value cannot be modified by an update operation. This counter is reset by a TRUNCATE TABLE operation.

A table can optionally have one field defined as data type ROWVERSION. If this field is defined, an insert operation automatically inserts an integer from the namespace-wide RowVersion counter into this field. An update operation automatically updates this integer with the current namespace-wide RowVersion counter value. No user-specified, calculated, or default value can be inserted for a ROWVERSION field. This counter cannot be reset.

Inserting SERIAL Values

An INSERT operation can specify one of the following values for a field with the SERIAL data type, with the following results:

  • No value, 0 (zero), or a nonnumeric value: Caché ignores the specified value, and instead increments this field's current serial counter value by 1, and inserts the resulting integer into the field.

  • A positive integer value: Caché inserts the user-specified value into the field, and changes the serial counter value for this field to this integer value.

Thus a SERIAL field contains a series incremental integer values. These values are not necessarily continuous or unique. For example, the following is a valid series of values for a SERIAL field: 1, 2, 3, 17, 18, 25, 25, 26, 27. Sequential integers are either Caché-generated or user-supplied; nonsequential integers are user-supplied. If you wish SERIAL field values to be unique, you must apply a UNIQUE constraint on the field.

Insert Computed Values

A field defined with COMPUTECODE may insert a value as part of the INSERT operation, unless the field is CALCULATED. If you supply a value for a COMPUTED field or if this field has a default value, INSERT stores this explicit value. Otherwise, the field value is computed, as follows:

  • COMPUTECODE: value is computed and stored upon INSERT, value is not changed upon UPDATE.

  • COMPUTECODE with COMPUTEONCHANGE: value is computed and stored upon INSERT, is recomputed and stored upon UPDATE.

  • COMPUTECODE with DEFAULT and COMPUTEONCHANGE: default value is stored upon INSERT, value is computed and stored upon UPDATE.

  • COMPUTECODE with CALCULATED or TRANSIENT: you cannot INSERT a value for this field because no value is stored. The value is computed when queried. However, if you attempt to insert a value into a calculated field, Caché performs validation on the supplied value and issues an error if the value is invalid. If the value is valid, Caché performs no insert operation, issues no SQLCODE error, and increments ROWCOUNT.

If the compute code contains a programming error (for example, divide by zero), the INSERT operation fails with an SQLCODE -415 error.

For further details, refer to Computing a field value on INSERT or UPDATE.

DEFAULT VALUES Clause

You can insert a row into a table that has all of its field values set to default values. Fields that have a defined default value are set to that value. Fields without a defined default value are set to NULL. This is done using the following command:

INSERT INTO Mytable DEFAULT VALUES

Fields defined with the NOT NULL constraint and no defined DEFAULT fail this operation with an SQLCODE -108.

Fields defined with the UNIQUE constraint can be inserted using this statement. If a field is defined with a UNIQUE constraint and no DEFAULT value, repeated invocations insert multiple rows with this UNIQUE field set to NULL. If a field is defined with a UNIQUE constraint and a DEFAULT value, this statement can only be used once. A second invocation fails with an SQLCODE -119.

DEFAULT VALUES inserts a row with a system-generated integer values for counter fields. These include the RowID, and the optional IDENTITY field, %Counter field, and ROWVERSION field.

Insert Query Results: INSERT with SELECT

A single INSERT can be used to insert multiple rows into a table by combining it with a SELECT statement. The SELECT extracts column data from multiple rows of one table, and the INSERT creates corresponding new rows containing this column data in another table.

You can limit the number of rows inserted by specifying a TOP clause in the SELECT statement. You can also use an ORDER BY clause in the SELECT statement to determine which rows will be selected by the TOP clause.

You can use a GROUP BY clause to insert only unique values of a field (or fields). Because GROUP BY by default converts values to uppercase for the purpose of grouping, it may be necessary to use %EXACT collation to preserve the letter case of the inserted values. This is shown in the following example:

INSERT INTO Sample.UniquePeople (Name,Age) SELECT Name,Age FROM Sample.Person WHERE Name IS NOT NULL GROUP BY %EXACT Name

An INSERT with SELECT operation sets the %ROWCOUNT variable to the number of rows inserted (either 0 or a positive integer).

The following example uses two embedded SQL programs to show this use of INSERT. The first example uses CREATE TABLE to create a new table SQLUser.MyStudents, and the second example populates this table with data extracted from Sample.Person. (Alternatively, you can create a new table from an existing table definition and insert data from the existing table in a single operation using the $SYSTEM.SQL.QueryToTable()Opens in a new tab method.)

To demonstrate this, please run the first embedded SQL program, then run the second. (It is necessary to use two embedded SQL programs here because embedded SQL cannot compile an INSERT statement unless the referenced table already exists.)

The following program creates the MyStudents table with two stored data fields, and one calculated field:

   ZNSPACE "Samples"
   WRITE !,"Creating table"
  &sql(CREATE TABLE SQLUser.MyStudents (
    StudentName VARCHAR(32),
    StudentDOB DATE,
    StudentAge INTEGER COMPUTECODE {SET {StudentAge}=
                       $PIECE(($PIECE($H,",",1)-{StudentDOB})/365,".",1)}
                       CALCULATED )
    )
  IF SQLCODE=0 {
    WRITE !,"Created table, SQLCODE=",SQLCODE }
  ELSEIF SQLCODE=-201 {
    WRITE !,"Table already exists, SQLCODE=",SQLCODE }

The following program uses INSERT to populate the MyStudents table with query results. Because the StudentAge field is calculated you cannot supply a value to this field; its value is calculated each time the MyStudents table is queried:

  ZNSPACE "Samples"
  WRITE !,"Populating table with data"
  NEW SQLCODE,%ROWCOUNT,%ROWID
  &sql(INSERT INTO SQLUser.MyStudents (StudentName,StudentDOB)
     SELECT Name,DOB
     FROM Sample.Person WHERE Age <= '21')
  IF SQLCODE=0 {
    WRITE !,"Number of records inserted=",%ROWCOUNT
    WRITE !,"Row ID of last record inserted=",%ROWID }
  ELSE {
    WRITE !,"Insert failed, SQLCODE=",SQLCODE }

Note that executing this INSERT program multiple times will succeed, but produces generally undesirable results. Each execution populates SQLUser.MyStudents with another set of records (%ROWCOUNT) with identical Name and DOB field values, automatically assigning each record a unique row ID (%ROWID).

To view the data, go to the Management Portal, select the Globals option for the SAMPLES namespace. Scroll to “SQLUser.MyStudentsD” and click the Data option.

The following programs display the MyStudents table data, then delete this table:

SELECT * FROM SQLUser.MyStudents ORDER BY StudentAge
  &sql(DROP TABLE SQLUser.MyStudents)
  IF SQLCODE=0 {WRITE !,"Table deleted" }
  ELSE {WRITE !,"SQLCODE=",SQLCODE," ",%msg }

By default, an Insert Query Results operation is an atomic operation. Either all of the specified rows are inserted in a table, or none of the rows are inserted. For example, if inserting one of the specified rows would violate foreign key referential integrity, the INSERT fails and no rows are inserted. This default is modifiable, as described below.

Copying Data into a Duplicate Table

You can use INSERT with SELECT * to copy the data from a table to a duplicate table, as long as the column order matches and the data types are compatible. The column names do not have to match.

INSERT INTO Sample.DupTable SELECT * FROM Sample.SrcTable
  • Data type compatible with data values: You can, for example, insert integer data from an INTEGER field into a VARCHAR field. The INSERT would fail with an SQLCODE -104 if any data value was incompatible with the destination data type. Refer to Data Type Precedence. You can use the CONVERT function to convert inserted data to the destination data type.

  • Data type length compatible with data values: The defined column data lengths do not have to match each other, they just have to match the actual data. For example, SrcTable can have a column FullName VARCHAR(60) and DupTable can have a corresponding PersonName VARCHAR(40). The INSERT will succeed as long as no existing FullName value is longer than 40 characters. The INSERT would fail with an SQLCODE -104 if any FullName value was longer than 40 characters.

  • Compatible column order: The two tables must have the same column order. Otherwise an SQLCODE -64 error is generated. The DDL CREATE TABLE operation lists the columns in the order defined. A persistent class that defines a table lists the columns in alphabetical order.

  • Compatible column count: The destination table can have additional columns beyond the ones copied. For example, SrcTable can have the columns FullName VARCHAR(60),Age INTEGER and DupTable can have PersonName VARCHAR(60),Years INTEGER,ShoeSize INTEGER. However, note that a persistent class that defines a table lists the columns in alphabetical order.

  • Private RowID: When a table is defined, the RowID field is defined as either public or private (hidden). The DDL CREATE TABLE operation defines the RowID as private by default. A persistent class that defines a table defines the RowID as public by default; to make it private you must specify the SqlRowIdPrivate class keyword when defining the persistent class.

    The easiest way to copy a table is to for both the Source and Destination tables to be defined with a private RowID. However, a foreign key can only refer to a table with a public RowID. Table copy operations behave as follows:

    • If Source private and Destination private: you can use INSERT SELECT with SELECT * to copy data to a duplicate table.

    • If Source public and Destination public: you cannot use INSERT SELECT to copy data to a duplicate table. An SQLCODE -111 error is generated.

    • If Source private and Destination public: you cannot use INSERT SELECT to copy data to a duplicate table. An SQLCODE -111 error is generated.

    • If Source is public and Destination private: you cannot use INSERT SELECT with SELECT * to copy data to a duplicate table. An SQLCODE -64 error is generated because of the presence of the RowID in one select list makes the select lists incompatible. You can use an INSERT SELECT with a list of all field names (not including the RowID) to copy data to a duplicate table. However, if the Source has a foreign key public RowID, the foreign key relationship will not be preserved for the destination table. The Destination will have new system-generated RowIDs.

  • Whether or not one of persistent classes that define these tables is Final has no effect on copying data into a duplicate table.

This operation can be used to copy existing data into a redefined table that will accept future column data values that would not have been valid in the original table.

SQLCODE Errors

By default, an INSERT is an all-or-nothing event: either the row (or rows) is inserted completely or not at all. Caché returns a status variable SQLCODE, indicating the success or failure of the INSERT. To insert a row into a table, the insert must meet all table, field name, and field value requirements, as follows.

Tables:

  • The table must already exist. Attempting an insert to a nonexistent table results in an SQLCODE -30 error. Because INSERT checks for the table's existence at compile time, a single compiled SQL program (such as an Embedded SQL program) cannot create a table (using CREATE TABLE) and then insert values into it.

  • The table cannot be defined as READONLY. Attempting to compile an INSERT that references a ReadOnly table results in an SQLCODE -115 error. Note that this error is now issued at compile time, rather than only occurring at execution time. See the description of READONLY objects in the Other Options for Persistent Classes chapter of Using Caché Objects.

  • If updating a table through a view, the view cannot be defined as WITH READ ONLY. Attempting to do so results in an SQLCODE -35 error. See the CREATE VIEW command for further details.

Field Names:

  • The field must exist. Attempting an insert to a nonexistent field results in an SQLCODE -29 error. To list all of the field names defined for a specified table, refer to Column Names and Numbers in the “Defining Tables” chapter of Using Caché SQL.

  • The insert must specify all required fields. Attempting to insert a row without specifying a value for a required field results in an SQLCODE -108 error.

  • The insert cannot include duplicate field names. Attempting to insert a row containing two fields with the same name results in an SQLCODE -377 error.

  • The insert cannot include fields that are defined as READONLY. Attempting to compile an INSERT that references a ReadOnly field results in an SQLCODE -138 error. Note that this error is now issued at compile time, rather than only occurring at execution time.

Field Values:

  • Every field value must pass data type validation. Attempting to insert a field value inappropriate to the field data type results in an SQLCODE -104 error. Note that this applies only to a inserted data value; a field’s DEFAULT value, if taken, does not have to pass data type validation or data size validation.

    • Data Type Mismatch: The field’s data type, not the type of the inserted data, determines appropriateness. For example, attempting to insert a string data type value into a date field fails unless the string passes date validation for the current mode; however, attempting to insert a date data type value into a string field succeeds, inserting the date as a literal string. You can use the CONVERT function to convert data to the destination data type.

    • Data Size Mismatch: A data value must be within the MAXLEN, MAXVAL, and MINVAL for the field. For example, attempting to insert a string longer than 24 characters into a field defined as VARCHAR(24), or attempting to insert a number larger than 127 into a field defined as TINYINT result an SQLCODE -104 error.

    • Numeric Type Mismatch: If an invalid DOUBLE number is supplied via ODBC or JDBC an SQLCODE -104 error occurs.

  • Every field value must pass display to logical mode conversion. Attempting to insert a field value in a format that cannot be converted to a Logical storage value results in an SQLCODE -146 error (for dates), or an SQLCODE -147 error (for times).

  • Every field value must pass data constraint validation:

    • A field defined as NOT NULL must be provided with a data value. If there is no DEFAULT value, not specifying a data value results in an SQLCODE -108 error, indicating that you have not specified a required field.

    • A field value must obey uniqueness constraints. Attempting to insert a duplicate field value in a field (or group of fields) with a uniqueness constraint results in an SQLCODE -119 error. This error is returned if the field has a UNIQUE data constraint, or if the unique fields constraint has been applied to a group of fields. This error can occur when you specify a duplicate value to a unique field, or to a primary key field, or when you do not specify a value and a second use of the field’s DEFAULT would supply a duplicate value. The SQLCODE -119 %msg string includes both the field and the value that violate the uniqueness constraint. For example <Table 'Sample.MyTable', Constraint 'MYTABLE_UNIQUE3', Field(s) FullName="Molly Bloom"; failed unique check> or <Table 'Sample.MyTable', Constraint 'MYTABLE_PKEY2', Field(s) FullName="Molly Bloom"; failed unique check>. For details on listing a table’s unique value and primary key field constraints and the naming of constraints, refer to Catalog Details: Constraints.

    • A field defined as a persistent class property with the VALUELIST parameter can only accept as a valid value one of the values listed in VALUELIST, or be provided with no value (NULL). VALUELIST valid values are case-sensitive. Specifying a data value that doesn’t match the VALUELIST values results in an SQLCODE -104 field value failed validation error.

  • Numbers are inserted in canonical form, but can be specified with leading and trailing zeros and multiple leading signs. However, in SQL, two consecutive minus signs are parsed as a single-line comment indicator. Therefore, attempting to specify a number with two consecutive leading minus signs results in an SQLCODE -12 error.

  • By default, an insert cannot specify values for fields for which the value is system-generated, such as the RowID, IDKey, or IDENTITY field. By default, attempting to insert a non-NULL field value for any of these fields results in an SQLCODE -111 error. Attempting to insert a NULL for one of these fields causes Caché to override the NULL with a system-generated value; the insert completes successfully and no error code is issued.

    If a field of data type ROWVERSION is defined, it is automatically assigned a system-generated counter value when a row is inserted. Attempting to insert a value into a ROWVERSION field results in an SQLCODE -138 error.

    An IDENTITY field can be made to accept user-specified values. By setting the SetIdentityInsert()Opens in a new tab method of the %SYSTEM.SQLOpens in a new tab class you can override the IDENTITY field default constraint and allow inserts of unique integer values to IDENTITY fields. (You can return the current setting for this constraint by calling the GetIdentityInsert()Opens in a new tab method.) Inserting an IDENTITY field value changes the IDENTITY counter so that subsequent system-generated values increment from this user-specified value. Attempting to insert a NULL for an IDENTITY field generates an SQLCODE -108 error.

    IDKey data has the following restriction. Because multiple IDKey fields in an index are delimited using the “||” (double vertical bar) characters, you cannot include this character string in IDKey field data.

  • An insert cannot include a field whose value violates foreign key referential integrity, unless the %NOCHECK restriction argument is specified, or the foreign key was defined with the NOCHECK keyword. Otherwise, attempting an insert that violates foreign key referential integrity results in an SQLCODE -121 error, with a %msg such as the following: <Table 'Sample.MyTable', Foreign Key Constraint 'MYTABLEFKey2', Field(s) FULLNAME failed referential integrity check>. For details on listing a table’s foreign key constraints and the naming of foreign key constraints, refer to Catalog Details: Constraints.

  • A field value cannot be a subquery. Attempting to specify a subquery as a field value results in an SQLCODE -144 error.

The INSERT Operation

This section describes the operational considerations when performing an INSERT:

Privileges

To insert one or more rows of data into a table, the user must have either table-level privileges or column-level privileges for that table.

Table-level Privileges

  • The user must have INSERT privilege on the specified table.

  • If inserting data from another table using a SELECT query, the user must have SELECT privilege on that table.

If the user is the Owner (creator) of the table, the user is automatically granted all privileges for that table. Otherwise, the user must be granted privileges for the table. Failing to do so results in an SQLCODE –99 error with the %msg User 'name' is not privileged for the operation. You can determine if the current user has the appropriate privileges by invoking the %CHECKPRIV command. You can use the GRANT command to assign the user table privileges. For further details, refer to Privileges in Using Caché SQL.

Table-level privileges are equivalent to (but not identical to) having column-level privileges on all columns of the table.

Column-level Privileges

If you do not have table-level INSERT privilege, you must have column-level INSERT privilege for at least one column of the table. To insert a specified value into a column, you must have column-level INSERT privilege for that column. Only those columns for which you have INSERT privilege receive the value specified in the INSERT command.

If you do not have column-level INSERT privilege for a specified column, Caché SQL inserts the column's default value (if defined), or NULL (if no default is defined). If you do not have INSERT privilege for a column that has no default and is defined as NOT NULL, Caché issues an SQLCODE -99 (Privilege Violation) error at Prepare time.

If the INSERT command specifies fields in a WHERE clause of a result set SELECT, you must have SELECT privilege for those fields if they are not data insert fields, and both SELECT and INSERT privileges for those fields if they are included in the result set.

When a property is defined as ReadOnly, the corresponding table field is also defined as ReadOnly. A ReadOnly field may only be assigned a value using InitialExpression or SqlComputed. Attempting to insert a value for a field for which you have column-level ReadOnly (SELECT or REFERENCES) privilege results in an SQLCODE -138 error: Cannot INSERT/UPDATE a value for a read only field.

You can use %CHECKPRIV to determine if you have the appropriate column-level privileges. See the GRANT command for privilege assignment.

Referential Integrity

If you do not specify %NOCHECK, Caché uses the system configuration setting to determine whether to perform foreign key referential integrity checking. You can set the system default as follows:

  • The $SYSTEM.SQL.SetFilerRefIntegrity()Opens in a new tab method call.

  • Go to the Management Portal, select System, Configuration, General SQL Settings. View and edit the current setting of Perform Referential Integrity Checks on Foreign Keys for INSERT, UPDATE, and DELETE. The default is “Yes”. If you change this setting, any new process started after changing it will have the new setting.

This setting does not apply to foreign keys that have been defined with the NOCHECK keyword.

During an INSERT operation, for every foreign key reference a shared lock is acquired on the corresponding row in the referenced table. This row is locked while performing referential integrity checking and inserting the row. The lock is then released (it is not held until the end of the transaction). This ensures that the referenced row is not changed between the referential integrity check and the completion of the insert operation.

However, if you specify the %NOLOCK restriction argument, no locking is performed either on the specified table or on the corresponding foreign key row in the referenced table.

Child Table Insert

During an INSERT operation on a child table, a shared lock is acquired on the corresponding row in the parent table. This row is locked while inserting the child table row. The lock is then released (it is not held until the end of the transaction). This ensures that the referenced parent row is not changed during the insert operation.

Atomicity

By default, INSERT, UPDATE, DELETE, and TRUNCATE TABLE are atomic operations. An INSERT either completes successfully or the whole operation is rolled back. If any of the specified rows cannot be inserted, none of the specified rows are inserted and the database reverts to its state before issuing the INSERT.

You can modify this default for the current process within SQL by invoking SET TRANSACTION %COMMITMODE. You can modify this default for the current process in ObjectScript by invoking the SetAutoCommit()Opens in a new tab method. The following options are available:

  • IMPLICIT or 1 (autocommit on) — The default behavior, as described above. Each INSERT constitutes a separate transaction.

  • EXPLICIT or 2 (autocommit off) — If no transaction is in progress, an INSERT automatically initiates a transaction, but you must explicitly COMMIT or ROLLBACK to end the transaction. In EXPLICIT mode the number of database operations per transaction is user-defined.

  • NONE or 0 (no auto transaction) — No transaction is initiated when you invoke INSERT. A failed INSERT operation can leave the database in an inconsistent state, with some of the specified rows inserted and some not inserted. To provide transaction support in this mode you must use START TRANSACTION to initiate the transaction and COMMIT or ROLLBACK to end the transaction.

You can determine the atomicity setting for the current process using the GetAutoCommit()Opens in a new tab method, as shown in the following ObjectScript example:

  DO $SYSTEM.SQL.SetAutoCommit($RANDOM(3))
  SET x=$SYSTEM.SQL.GetAutoCommit()
  IF x=1 {
    WRITE "Default atomicity behavior",!
    WRITE "automatic commit or rollback" }
  ELSEIF x=0 {
    WRITE "No transaction initiated, no atomicity:",!
    WRITE "failed DELETE can leave database inconsistent",!
    WRITE "rollback is not supported" }
  ELSE { WRITE "Explicit commit or rollback required" }

Transaction Locking

If you do not specify %NOLOCK, the system automatically performs standard record locking on INSERT, UPDATE, and DELETE operations. Each affected record (row) is locked for the duration of the current transaction.

The default lock threshold is 1000 locks per table. This means that if you insert more than 1000 records from a table during a transaction, the lock threshold is reached and Caché automatically escalates the locking level from record locks to a table lock. This permits large-scale inserts during a transaction without overflowing the lock table.

Caché applies one of the two following lock escalation strategies:

  • “E”-type lock escalation: Caché uses this type of lock escalation if the following are true: (1) the class uses %CacheStorage (you can determine this from the Catalog Details in the Management Portal SQL schema display). (2) the class either does not specify an IDKey index, or specifies a single-property IDKey index. “E”-type lock escalation is described in the LOCK command in the Caché ObjectScript Reference.

  • Traditional SQL lock escalation: The most likely reason why a class would not use “E”-type lock escalation is the presence of a multi-property IDKey index. In this case, each %Save increments the lock counter. This means if you do 1001 saves of a single object within a transaction, Caché will attempt to escalate the lock.

For both lock escalation strategies, you can determine the current systemwide lock threshold value using the $SYSTEM.SQL.GetLockThreshold()Opens in a new tab method. The default is 1000. This systemwide lock threshold value is configurable:

  • Using the $SYSTEM.SQL.SetLockThreshold()Opens in a new tab method.

  • Using the Management Portal. Go to System, Configuration, General SQL Settings. View and edit the current setting of Lock Threshold. The default is 1000 locks. If you change this setting, any new process started after changing it will have the new setting.

You must have USE permission on the %Admin Manage Resource to change the lock threshold. Caché immediately applies any change made to the lock threshold value to all current processes.

On potential consequence of automatic lock escalation is a deadlock situation that might occur when an attempt to escalate to a table lock conflicts with another process holding a record lock in that table. There are several possible strategies to avoid this: (1) increase the lock escalation threshold so that lock escalation is unlikely to occur within a transaction. (2) substantially lower the lock escalation threshold so that lock escalation occurs almost immediately, thus decreasing the opportunity for other processes to lock a record in the same table. (3) apply a table lock for the duration of the transaction and do not perform record locks. This can be done at the start of the transaction by specifying LOCK TABLE, then UNLOCK TABLE (without the IMMEDIATE keyword, so that the table lock persists until the end of the transaction), then perform inserts with the %NOLOCK option.

Automatic lock escalation is intended to prevent overflow of the lock table. However, if you perform such a large number of inserts that a <LOCKTABLEFULL> error occurs, INSERT issues an SQLCODE -110 error.

For further details on transaction locking refer to Transaction Processing in the “Modifying the Database” chapter of Using Caché SQL.

Row-Level Security

Caché row-level security permits INSERT to add a row even if the row security is defined so that you will not be permitted to subsequently access the row. To ensure that an INSERT does not prevent you from subsequent SELECT access to the row, it is recommended that you perform the INSERT through a view that has a WITH CHECK OPTION. For further details, refer to CREATE VIEW.

Microsoft Access

To use INSERT to add data to a Caché table using Microsoft Access, either mark the table RowID as private or define a unique index on one or more additional fields.

Embedded SQL and Dynamic SQL Examples

The following Embedded SQL example creates a new table SQLUser.MyKids. The examples that follow use INSERT to populate this table with data. After the INSERT examples, an example is provided to delete SQLUser.MyKids.

CreateTable
   &sql(CREATE TABLE SQLUser.MyKids (
    KidName VARCHAR(16) UNIQUE NOT NULL,
    KidDOB INTEGER NOT NULL,
    KidPetName VARCHAR(16) DEFAULT 'no pet') )
  IF SQLCODE=0 {
    WRITE !,"Table created" }
  ELSEIF SQLCODE=-201 {WRITE !,"Table already exists"  QUIT}
  ELSE {
    WRITE !,"CREATE TABLE failed. SQLCODE=",SQLCODE }

The following Embedded SQL example inserts a row with two field values (the third field, KidPetName, takes a default value). Note that the table schema name is supplied as a schema search path by the #sqlcompile path macro directive:

EmbeddedSQLInsertByColName
  #sqlcompile path=Sample
  NEW SQLCODE,%ROWCOUNT,%ROWID
  &sql(INSERT INTO MyKids (KidName,KidDOB) VALUES 
   ('Molly',60000))
  IF SQLCODE=0 {
    WRITE !,"Insert succeeded"
    WRITE !,"Row count=",%ROWCOUNT
    WRITE !,"Row ID=",%ROWID
    QUIT }
  ELSEIF SQLCODE=-119 {
    WRITE !,"Duplicate record not written",!
    WRITE %msg,!
    QUIT }
  ELSE {
    WRITE !,"Insert failed, SQLCODE=",SQLCODE }

The following Embedded SQL example inserts a row with three field values using the table's column order:

EmbeddedSQLInsertByColOrder
  NEW SQLCODE,%ROWCOUNT,%ROWID
  &sql(INSERT INTO SQLUser.MyKids VALUES ('Josie','40100','Fido') )
  IF SQLCODE=0 {
    WRITE !,"Insert succeeded"
    WRITE !,"Row count=",%ROWCOUNT
    WRITE !,"Row ID=",%ROWID
    QUIT }
  ELSEIF SQLCODE=-119 {
    WRITE !,"Duplicate record not written",!
    WRITE %msg,!
    QUIT }
  ELSE {
    WRITE !,"Insert failed, SQLCODE=",SQLCODE }

The following Embedded SQL example uses host variables to insert a row with two field values. The INSERT syntax used here specifies column=value pairs:

EmbeddedSQLInsertHostVars
  #sqlcompile path=Sample
  NEW SQLCODE,%ROWCOUNT,%ROWID
  SET x = "Sam"
  SET y = "57555"
  &sql(INSERT INTO MyKids SET KidName=:x,KidDOB=:y )
  IF SQLCODE=0 {
    WRITE !,"Insert succeeded"
    WRITE !,"Row count=",%ROWCOUNT
    WRITE !,"Row ID=",%ROWID
    QUIT }
  ELSEIF SQLCODE=-119 {
    WRITE !,"Duplicate record not written",!
    WRITE %msg,!
    QUIT }
  ELSE {
    WRITE !,"Insert failed, SQLCODE=",SQLCODE }

The following Embedded SQL example uses a host variable array to insert a row with three field values. Array elements are numbered in column order. Note that user-supplied array values start with myarray(2); the first array element corresponds to the RowID, which is automatically supplied and cannot be user-defined:

EmbeddedSQLInsertHostVarArray
  #sqlcompile path=Sample
  NEW SQLCODE,%ROWCOUNT,%ROWID
  SET myarray(2)="Deborah"
  SET myarray(3)=60200
  SET myarray(4)="Bowie"
  &sql(INSERT INTO MyKids VALUES :myarray())
  IF SQLCODE=0 {
    WRITE !,"Insert succeeded"
    WRITE !,"Row count=",%ROWCOUNT
    WRITE !,"Row ID=",%ROWID
    QUIT }
  ELSEIF SQLCODE=-119 {
    WRITE !,"Duplicate record not written",!
    WRITE %msg,!
    QUIT }
  ELSE {
    WRITE !,"Insert failed, SQLCODE=",SQLCODE }

The following Dynamic SQL example uses the %SQL.StatementOpens in a new tab class to insert a row with three field values. Note that the table schema name is supplied as a schema search path in the %New()Opens in a new tab method:

COSDynamicSQLInsert
  SET x = "Noah"
  SET y = "61000"
  SET z = "Luna"
  SET sqltext = "INSERT INTO MyKids (KidName,KidDOB,KidPetName) VALUES (?,?,?)"
  SET tStatement = ##class(%SQL.Statement).%New(0,"Sample")
  SET qStatus = tStatement.%Prepare(sqltext)
    IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
  SET rtn = tStatement.%Execute(x,y,z)
  IF rtn.%SQLCODE=0 {
    WRITE !,"Insert succeeded"
    WRITE !,"Row count=",rtn.%ROWCOUNT
    WRITE !,"Row ID=",rtn.%ROWID }
  ELSEIF rtn.%SQLCODE=-119 {
    WRITE !,"Duplicate record not written",!,rtn.%Message
    QUIT }
  ELSE {
    WRITE !,"Insert failed, SQLCODE=",rtn.%SQLCODE }

For further details, refer to the Embedded SQL and Dynamic SQL chapters in Using InterSystems SQL.

The following Embedded SQL example displays the inserted records, then deletes the SQLUser.MyKids table:

DisplayAndDeleteTable
  SET myquery = "SELECT * FROM SQLUser.MyKids"
  SET tStatement = ##class(%SQL.Statement).%New()
  SET qStatus = tStatement.%Prepare(myquery)
    IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
  SET rset = tStatement.%Execute()
  DO rset.%Display()
  WRITE !,"End of data"
  &sql(DROP TABLE SQLUser.MyKids)
   IF SQLCODE=0 {
    WRITE !,"Deleted table"
    QUIT }
  ELSE {
    WRITE !,"Table delete failed, SQLCODE=",SQLCODE }

The following Embedded SQL example demonstrates the use of a host variable arrays. Note that with a host variable array, you can use a dynamic local array with an unspecified last subscript to pass an array of values to INSERT at runtime. For example:

  NEW SQLCODE,%ROWCOUNT,%ROWID
  &sql(INSERT INTO Sample.Employee VALUES :emp('profile',))
  WRITE !,"SQL Error code: ",SQLCODE," Row Count: ",%ROWCOUNT

causes each field in the inserted "Employee" row to be set to:

emp("profile",col)

where "col" is the field’s column number in the Sample.Employee table.

The following example shows how the results of a SELECT query can be used as the data input into an INSERT statement, supplying the data for multiple rows:

INSERT INTO StudentRoster (NAME,GPA,ID_NUM)
     SELECT FullName,GradeAvg,ID
     FROM temp WHERE SchoolYear = '2004'

See Also

FeedbackOpens in a new tab