INSERT (SQL)

• INSERT INTO table (column, column2, ...) VALUES (value, value2, ...) inserts a row of values into the specified columns of a table. The values in the VALUES clause must correspond positionally with the column names in the column list.

  By default, an INSERT is an all-or-nothing event: either a row is inserted completely or not at all. InterSystems IRIS® 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 requirements described in the table, column, and value arguments.

  This statement inserts a new row into the Sample.Records table, setting the value of the StatusDate column to '05/12/22' and the value of the Status column to 'Purged'.

  INSERT INTO Sample.Records (StatusDate,Status) VALUES ('05/12/22','Purged')

  

  Example: Insert Rows into Table Using Specified Values

• INSERT INTO table VALUES (value, value2, ...) inserts the table row of values in column number order. The data values must correspond positionally to the defined column list. You must specify a value for every specifiable table column. You cannot use defined default values, but you can specify an empty string as a value. Because the RowID column is not specifiable, do not include a RowID value in the VALUES list.

  This statement inserts a row of four values into the Sample.Address table in order. The statement assumes that the table contains exactly four columns whose data corresponds to the values being inserted, for example: Street, City, State, and ZipCode.

  INSERT INTO Sample.Address VALUES ('22 Main St.','Anytown','PA','65342')

  

  Example: Insert Rows into Table Using Specified Values

• INSERT INTO table SET column = value, column2 = value2, ... inserts a row of values by explicitly setting the values of specific columns.

  This statement performs the same operation as in the INSERT INTO table (column, column2, ...) VALUES (value, value2, ...) syntax.

  INSERT INTO Sample.Records SET StatusDate='05/12/22',Status='Purged'

  

  Example: Insert Rows into Table Using Specified Values

• INSERT INTO table DEFAULT VALUES inserts a row into a table that contains only default column values.

  • Columns with a defined default value are set to that value.

  • Columns without a defined default value are set to NULL.

  This statement inserts a row of default column values into the Sample.Person table.

  INSERT INTO Sample.Person DEFAULT VALUES

  

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

  Columns defined with the UNIQUE constraint can be inserted using this statement. If a column is defined with a UNIQUE constraint and no DEFAULT value, repeated DEFAULT VALUES class insert multiple rows with this UNIQUE column set to NULL. If a column is defined with a UNIQUE constraint and a DEFAULT value, you can use this statement only once. A second call fails with an SQLCODE -119 error.

  DEFAULT VALUES inserts a row with a system-generated integer values for counter columns, including the RowID column, IDENTITY column, SERIAL (%Counter) column, and ROWVERSION column.

• INSERT INTO table VALUES :array() inserts values from an array, specified as a host variable, into the columns of a table. You can use this syntax with Embedded SQL only. The values in this array must implicitly correspond to the columns of the row in column number order. You must specify a value for each specifiable column. An INSERT using column order cannot take defined column default values.

  This statement populates the array at runtime, enabling you to delay specifying which columns to insert until runtime. All other inserts require that you specify which columns are to be inserted when the INSERT is prepared. This syntax cannot be used with a linked table. Attempting to do so results in an SQLCODE -155 error.

  This class method uses embedded SQL to insert an array into the Sample.FullName table. myarray(1) is reserved for the RowID column and is therefore not specified.

  ClassMethod EmbeddedSQLInsertHostVarArray()
  {
    set myarray(2)="Juanita"
    set myarray(3)="Pybus"
    &sql(INSERT INTO Sample.FullName VALUES :myarray())
    if SQLCODE '= 0 {
      write !, "Insert failed, SQLCODE= ", SQLCODE, ! ,%msg
      quit
    }
    write !,"Insert succeeded" quit
  }

  

  For more details on host variables and arrays, see Host Variable as a Subscripted Array.

  Examples:

  • Insert Data Using Embedded SQL

  • Insert Stream Data into Table

• INSERT INTO table query inserts rows of data that come from the result set of a SELECT query. The columns in the result set must match the columns in the table. You can use INSERT with a SELECT to populate a table with existing data extracted from other tables.

  This statement populates a table, Sample.DupTable, with the same values as the source table, Sample.SrcTable. The two tables must have the same number of columns, the same column names, and the same column order.

  INSERT INTO Sample.DupTable SELECT * FROM Sample.SrcTable

  

  Example: Insert Data from Another Table Using a SELECT Query

• INSERT INTO table (column, column2, ...) query inserts rows of data from the query result set into the specified columns. The INSERT statement sets unspecified column values in the row to NULL or to their default values.

  This statement inserts the query result set data from the Name and DOB columns of Sample.Person into the matching columns of the Sample.Kids table.

  INSERT INTO Sample.Kids (Name,DOB) SELECT Name,DOB FROM Sample.Person WHERE Age <= 18

  

  Example: Insert Data from Another Table Using a SELECT Query

• INSERT table ... omits the INTO keyword.

• INSERT %keyword [INTO] table ... sets one or more %keyword options, separated by spaces. Valid options are %NOCHECK, %NOFPLAN, %NOINDEX, %NOJOURN, %NOLOCK, %NOTRIGGER, %PROFILE, and %PROFILE_ALL.

The name of the table or view on which to perform the insert operation. A table 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 specified, or the default schema name.

You can also perform INSERT using a subquery in place of the table argument. For example:

INSERT INTO (SELECT column1 AS c1 FROM MyTable) (c1) VALUES ('test')

To insert a row into a table, you must have appropriate table-level privileges.

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.

To insert a row into a table, you must have appropriate column-level privileges.

A scalar expression, or comma-separated list of scalar expressions, specified in the VALUES clause that supplies the data values for the corresponding columns in column. Specifying fewer values than columns generates an SQLCODE -62 error. Specifying more values than columns generates an SQLCODE -116 error.

To insert a row into a table, the column values specified in value must meet these requirements:

• Each column value must pass data type validation. Attempting to insert a column value inappropriate to the column data type results in an SQLCODE -104 error. This requirement applies only to an inserted data value. A column that takes a DEFAULT value does not have to pass data type validation or data size validation. The data type of the column, not the data type of the inserted data value, determines appropriateness. For example, attempting to insert a string value into a date column fails unless the string passes date validation for the current mode. However, attempting to insert a date value into a string column succeeds. INSERT inserts the date into the table as a literal string. To convert data to the destination data type, use the CONVERT function.

• Each data value must be within the MAXLEN, MAXVAL, and MINVAL for the column. For example, attempting to insert a string longer than 24 characters into a column defined as VARCHAR(24), or attempting to insert a number larger than 127 into a column defined as TINYINT, results in an SQLCODE -104 error.

• Supplying an invalid DOUBLE number via ODBC or JDBC results in an SQLCODE -104 error.

• Inserted data values must pass display to logical mode conversion. InterSystems SQL stores data in logical mode format. For some data types, the logical format might differ from the display format. For example, date data is stored as an integer count of days, time data is stored as a count of seconds from midnight, and %List data is stored as an encoded string. Other data types, such as strings and numbers, require no conversion. Attempting to insert a value in a format that cannot be converted to its logical storage value results in an error. For more details on mode conversions, see Data Display Options

• Every data value must pass data constraint validation for the column it is being inserted into.

  • A column defined as NOT NULL must be provided with a data value. If the column has no DEFAULT value, not specifying a data value results in an SQLCODE -108 error.

  • Data values must obey UNIQUE data constraints defined on a column or group of columns. Attempting to insert duplicate values into a unique column (such as the primary key column) or a unique column group results in an SQLCODE -119 error. This error can also occur when you do not specify a value and a second use of the column’s DEFAULT would supply a duplicate value.

  • A column defined as a persistent class property with the VALUELIST parameter can accept only values listed in VALUELIST or no value (NULL). VALUELIST values are case-sensitive. Specifying a data value that does not match the VALUELIST values results in an SQLCODE -104 column 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 columns for which the value is system-generated, such as the RowID, IDKey, or IDENTITY column. By default, attempting to insert a non-NULL value for any of these columns results in an SQLCODE -111 error. Attempting to insert a NULL for one of these columns causes InterSystems IRIS to override the NULL with a system-generated value, which does not produce an error.

  • If the table defines a ROWVERSION column, that column is automatically assigned a system-generated counter value when a row is inserted. Attempting to insert a value into a ROWVERSION column results in an SQLCODE -138 error.

  • An IDENTITY column can be made to accept user-specified values. By setting the SetOption("IdentityInsert")Opens in a new tab method, you can override the IDENTITY column default constraint and allow inserts of unique integer values to IDENTITY columns. To return the current setting for this constraint, call the GetOption("IdentityInsert")Opens in a new tab method. Inserting an IDENTITY column 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 column generates an SQLCODE -108 error.

  • IDKey data has the following restriction. Because multiple IDKey columns in an index are delimited using the “||” (double vertical bar) characters, you cannot insert data values with this character string into IDKey columns.

• Values being inserted must not violate foreign key referential integrity, unless the INSERT command specifies the %NOCHECK keyword 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. For details on listing a table’s foreign key constraints and the naming of foreign key constraints, refer to Catalog Details: Constraints.

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

Non-Display Character Values

To insert values containing non-display characters, use the CHAR function and the concatenation operator. For example, this statement inserts a string consisting of the letter “A”, a line feed character, and the letter “B”:

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

To concatenate the results of a function, you must use the || concatenation operator, not the _ concatenation operator used in ObjectScript.

A SELECT query, the result set of which supplies the data values for the corresponding columns specified in column.

The SELECT query extracts column data from one or more tables and the INSERT command creates corresponding new rows in its table containing this column data. Corresponding columns can have different column names and column lengths, so long as the inserted data can fit in the table column. If the corresponding columns do not pass data type and length validation checks, InterSystems SQL generates an SQLCODE -104 error.

To limit the number of rows inserted, specify a TOP clause in the SELECT statement. To determine which of these top rows the query selects, use an ORDER BY clause in the SELECT statement.

To insert only unique values of certain columns, specify a GROUP BY clause in the query. By default, GROUP BY converts values to uppercase for the purpose of grouping. To preserve the letter case of inserted values, specify %EXACT collation in the query. For 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).

A dynamic local array of values specified as a host variable. This value applies to Embedded SQL only.

The lowest subscript level of the array must be unspecified. Thus :myupdates(), :myupdates(5,), and :myupdates(1,1,) are all valid specifications.

Keyword options that configure INSERT processing. You can specify keyword options in any order. Separate multiple keyword options by spaces.

This table describes the keyword options that you can specify:

This example shows that various ways that you can insert a new row of values into a table.

Create a table containing company data. This table has two required columns: the name of the company, which must be unique, and the country in which the company headquarters is located. The second column, Revenue, is not required and has a default value of 0.

CREATE TABLE Sample.Company (
    Name VARCHAR(20) UNIQUE NOT NULL,
    Revenue INTEGER DEFAULT 0,
    Country VARCHAR(10) NOT NULL)

Insert a row of data into the table. The column values must be specified in the same order as the table’s column order.

INSERT INTO Sample.Company VALUES ('CompanyA',10000,'BEL')

Insert another row of data, this time specifying the column names to insert data into. Because this statement omits the Revenue column, InterSystems SQL sets this column value to its default value of 0.

INSERT INTO Sample.Company (Name,Country) VALUES ('CompanyB','CAN')

Insert a third row of data, this time using the SET column=value syntax. Note that the column=value pairs do not have to be in table column order.

INSERT INTO Sample.Company Set Name = 'CompanyC', Country = 'ECU', Revenue = 25000

View the inserted data, ordered by their revenue.

SELECT * FROM Sample.Company ORDER BY Revenue DESC

Delete the table when you are done.

DROP TABLE Sample.Company

These examples show the different types of data values you can insert into a stream field by using embedded SQL.

For any table, you can insert a string literal or a host variable containing a string literal. For example:

    set literal="Technique 1"
    &sql(INSERT INTO MyStreamTable (MyStreamField) VALUES (:literal))

You can also insert an object reference (OREF) into a stream object for any non-sharded table. InterSystems IRIS opens this object and copies its contents into the new stream field. For example:

    set oref=##class(%Stream.GlobalCharacter).%New()
    do oref.Write("Technique non-shard 1")

    //do the insert; use an actual OREF
    &sql(INSERT INTO MyStreamTable (MyStreamField) VALUES (:oref))

Alternatively, you can insert a string version of an OREF into a stream object:

    set oref=##class(%Stream.GlobalCharacter).%New()
    do oref.Write("Technique non-shard 2")

    //next line converts OREF to a string OREF
    set string=oref_""

    //do the insert
    &sql(INSERT INTO MyStreamTable (MyStreamField) VALUES (:string))

For a sharded table, you can insert an object ID (OID) using a temporary stream object stored in the ^IRIS.Stream.Shard global:

    set clob=##class(%Stream.GlobalCharacter).%New("Shard")
    do clob.Write("Technique Sharded Table 1")
    set sc=clob.%Save() // Handle $$$ISERR(sc)
    set ClobOid=clob.%Oid()

    &sql(INSERT INTO MyStreamTable (MyStreamField) VALUES (:ClobOid))

Attempting to insert an improperly defined stream value results in an SQLCODE -412 error.

For more details and examples, see Inserting Data into Stream Data Fields.

This Embedded SQL example uses a host variable array to insert a row with three column values. Array elements are numbered in column order. Specified array values must start with the second element, in this case company(2). The first array element corresponds to the RowID column, which is automatically supplied and cannot be defined:

  SET company(2)="Company1"
  SET company(3)=15000
  SET company(4)="JPN"
  &sql(INSERT INTO Sample.Company VALUES :company())

This embedded SQL example uses a dynamic local array with an unspecified last subscript to pass an array of values to INSERT at runtime.

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

The previous statements causes each column in the inserted "Employee" row to be set to the following, where "col" is the column’s number in the Sample.Employee table.

emp("profile",col)

This class method uses Dynamic SQL to insert values into a table based on arguments passed into the method.

ClassMethod DynamicSQLInsert(name As %String, revenue As %Integer, country As %String)
{
  set sqltext = "INSERT INTO Sample.Company (Name,Revenue,Country) 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(name,revenue,country)
  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  }
}

This example shows how to populate a table with data extracted from another table by using an INSERT with SELECT operation. This example assumes that you have a previously defined Sample.Person table that contains Name, DOB, and Age columns. You can download such a table from GitHub at https://github.com/intersystems/Samples-DataOpens in a new tab. For download instructions, see Downloading Samples for Use with InterSystems Products.

Create a table called MyStudents. This table contains name and date-of-birth columns, both of which are specified, and an age column, which is calculated from the date-of-birth column.

CREATE TABLE MyStudents (
  StudentName VARCHAR(32),
  StudentDOB DATE,
  StudentAge INTEGER COMPUTECODE {set {StudentAge} =
                       $piece(($piece($horolog,",",1)-{StudentDOB})/365,".",1)}
                       CALCULATED)

Insert student data from the Sample.Person table into the MyStudents table. Use a SELECT query that selects the people that are 21 and under. You can use either of these two queries. Because the column order of the two tables match and only the first two columns are stored, the column names can be omitted.

INSERT INTO MyStudents (StudentName,StudentDOB)
  SELECT Name,DOB
  FROM Sample.Person WHERE Age <= 21

INSERT INTO MyStudents
  SELECT Name,DOB
  FROM Sample.Person WHERE Age <= 21

Display the results, ordered by age.

SELECT * FROM MyStudents ORDER BY StudentAge

Delete the table when you are done.

DROP TABLE MyStudents

Another use of INSERT with SELECT is to create a duplicate table from an existing table. You can use this operation 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. For more details, see Copy Data into a Duplicate Table.

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

• When inserting any data into a table, you must have INSERT privilege on the table.

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

The Owner (creator) of the table is automatically granted all privileges for that table. If you are not the Owner, you must be granted privileges for the table. Failing to do so results in an SQLCODE -99 error.

To determine if you have the appropriate privileges, use %CHECKPRIV. To assign table privileges to a user, use GRANT. For more details, see Privileges.

To insert into a sharded table, you must have INSERT privileges for the target tables. Failing to have these privileges results in an SQLCODE -253 error.

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

If you do not have table-level INSERT privilege, 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, InterSystems 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, InterSystems IRIS issues an SQLCODE -99 (Privilege Violation) error at Prepare time.

If the INSERT command specifies columns in the WHERE clause of a SELECT subquery, you must have these privileges:

• SELECT privilege for those columns if they are not data insert columns.

• Both SELECT and INSERT privileges for those columns if they are included in the result set.

When a property is defined as ReadOnly, the corresponding table column is also defined as ReadOnly. You can assign a value to a ReadOnly column only by using InitialExpression or SqlComputed. Attempting to insert a value into column for which you have column-level ReadOnly (SELECT or REFERENCES) privilege results in an SQLCODE -138 error.

To determine if you have the appropriate privileges, use %CHECKPRIV. To assign column-level privileges to a user, use GRANT. For more details, see Privileges.

InterSystems IRIS 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, perform the INSERT through a view that has a WITH CHECK OPTION. For more details, see CREATE VIEW.

When using Fast Insert using ODBC, you may set fields TIMESTAMP or POSIX typed fields using the integer or _int64 data types. The provided number will be treated as a $HOROLOG value.

String values that are converted to doubles will be validated, ensuring that the String represents a numeric value. This validation also accepts, in any case, “INF”, “infinity”, and “NaN” values, which may be preceded by a + or - sign.

Values being converted from Numeric to integer will be truncated, not rounded.

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 SetOption()Opens in a new tab method, using this syntax:

SET status=$SYSTEM.SQL.Util.SetOption("AutoCommit",intval,.oldval)

The following intval integer options are available:

• 1 or IMPLICIT (autocommit on — default) — Each INSERT constitutes a separate transaction.

• 2 or EXPLICIT (autocommit off) — If no transaction is in progress, 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.

• 0 or NONE (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 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.

  A sharded table is always in no auto-transaction mode, which means all inserts, updates, and deletes to sharded tables are performed outside the scope of a transaction.

To determine the atomicity setting for the current process, use the GetOption("AutoCommit")Opens in a new tab method, as shown in this ObjectScript example:

  SET stat=$SYSTEM.SQL.Util.SetOption("AutoCommit",$RANDOM(3),.oldval)
  IF stat'=1 {WRITE "SetOption failed:" DO $System.Status.DisplayError(qStatus) QUIT}
    SET x=$SYSTEM.SQL.Util.GetOption("AutoCommit")
    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" }

If the %NOLOCK keyword is not specified, 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. If you insert more than 1000 records from a table during a transaction, the lock threshold is reached and InterSystems IRIS 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.

InterSystems IRIS applies one of these lock escalation strategies:

• “E”-type escalation locks — InterSystems IRIS uses this lock escalation if the following are true:

  1. The class of the table uses %Storage.Persistent. 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.

  For details on “E”-type lock escalation, see LOCK.

• Traditional SQL lock escalation — This lock escalation can take place when the class has 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, InterSystems IRIS attempts to escalate the lock.

For both lock escalation strategies, you can determine the current system-wide lock threshold value using the $SYSTEM.SQL.Util.GetOption("LockThreshold")Opens in a new tab method. The default is 1000. You can configure the system-wide lock threshold using one of these options:

• Call the $SYSTEM.SQL.Util.SetOption("LockThreshold")Opens in a new tab method.

• In the Management Portal, select System Administration, Configuration, SQL and Object Settings, SQL. Display and edit the current setting of Lock escalation threshold. The default is 1000 locks. If you change this setting, any new process started after changing it will have the new setting.

To change the lock threshold, you must have USE permission on the %Admin Manage Resource. InterSystems IRIS immediately applies any change made to the lock threshold value to all current processes.

One 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, see Transaction Processing.

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.

You can use INSERT with SELECT * to copy the data from one table into a duplicate table, as long as the column order matches and the data types are compatible. Use this operation 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. Sample syntax:

INSERT INTO Sample.DupTable SELECT * FROM Sample.SrcTable

The column names do not have to match, but the data in the source and destination tables must meet these requirements:

• The data type of the source table values must be compatible with the data type of the destination table columns. You can, for example, insert integer data from an INTEGER column into a VARCHAR column, because the INTEGER value can be converted to a VARCHAR. If any data value is incompatible, the INSERT fails with an SQLCODE -104 error. To explicitly convert inserted data to the destination data type, you can use the CONVERT function.

• The data type length of the source table values must be compatible with the length of the destination table columns. The defined column data lengths do not have to match each other, they just have to match the actual data. For example, suppose SrcTable has a FullName VARCHAR(60) column and DupTable has a corresponding PersonName VARCHAR(40) column. If no existing FullName value is longer than 40 characters, the INSERT succeeds. If any FullName is longer than 40 characters, the INSERT fails with an SQLCODE -104 error.

• The two tables must have a compatible column order or the INSERT command fails with an SQLCODE -64 error. 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.

• The tables must have a compatible column count, but the destination table can have additional columns beyond the ones copied. For example, SrcTable can have the columns FullName VARCHAR(60) and Age INTEGER, and DupTable can have the columns PersonName VARCHAR(60), Years INTEGER, and ShoeSize INTEGER.

• If either the source and destination table defines a public RowID, copying data is restricted, as shown in this table:

  Source Table	Destination Table	Copy Operation Behavior
  Private RowID	Private RowID	You can use INSERT SELECT with SELECT * to copy data to a duplicate table.
  Public RowID	Public RowID	You cannot use INSERT SELECT to copy data to a duplicate table. An SQLCODE -111 error is generated.
  Private RowID	Public RowID	You cannot use INSERT SELECT to copy data to a duplicate table. An SQLCODE -111 error is generated.
  Public RowID	Private RowID	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 column 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 is not preserved for the destination table. The Destination receives new system-generated RowIDs. If the source table has a foreign key public RowID, and you want the destination table to have the same foreign key relationship, you must define the destination table with the %CLASSPARAMETER ALLOWIDENTITYINSERT=1 in CREATE TABLE. If a table is defined as ALLOWIDENTITYINSERT=1, this setting cannot be changed by the SetOption("IdentityInsert")Opens in a new tab method.

  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. However, a foreign key can only refer to a table with a public RowID.

If the persistent class of a source or destination table defines the Final keyword, this keyword has no effect on copying data into a duplicate table.

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. For example, consider a persistent class has a property, PAddress, that references a serial object contain the properties Street, City, and Country, in that order. You can insert values of this property in these ways:

• Use the referencing field to insert values for multiple %SerialObject properties as a %List structure. For example:

  INSERT INTO MyTable SET PAddress=$LISTBUILD('123 Main St.','Newtown','USA')

  

  INSERT INTO MyTable (PAddress) VALUES ($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.

  This type of insert might not perform validation of %SerialObject property values. Therefore, after inserting %SerialObject property values using a %List structure, use the $SYSTEM.SQL.Schema.ValidateTable() method to perform Table Data Validation.

• Use underscore syntax to insert values for individual %SerialObject properties in any order. For example:

  INSERT INTO MyTable SET PAddress_City='Newtown',PAddress_Street='123 Main St.',PAddress_Country='USA'

  

  Unspecified serial object properties default to NULL.

  This type of insert performs validation of %SerialObject property values.