Home
SQL programmingYou can use the HCL OneDB™ implementation of the SQL language to develop applications for HCL OneDB™ database servers.
Guide to SQL: SyntaxThe HCL OneDB™ Guide to SQL: Syntax describes the syntax of the statements, data types, expressions, operators, and built-in functions of the HCL OneDB™ dialect of the SQL language.
SQL statementsThis chapter describes the syntax and semantics of SQL statements that are recognized by HCL OneDB™.
INSERT statementUse the INSERT statement to insert one or more new rows into a table or view, or to insert one or more elements into an SQL or collection variable.
VALUES Clause
Inserting Values into Collection Columns

Inserting Values into Collection Columns

You can use the VALUES clause to insert values into a collection column. For more information, see Collection Constructors.

For example, suppose you define the tab1 table as follows:

CREATE TABLE tab1 
   (
   int1 INTEGER, 
   list1 LIST(ROW(a INTEGER, b CHAR(5)) NOT NULL),
   dec1 DECIMAL(5,2)
   );

The following INSERT statement inserts a row into tab1:

INSERT INTO tab1 VALUES 
   (
   10,
   LIST{ROW(1,'abcde'), 
      ROW(POW(3,3), '=27'), 
      ROW(ROUND(ROOT(126)), '=11')},
   100
   );

The collection column, list1, in this example, has three elements. Each element is an unnamed row type with an INTEGER field and a CHAR(5) field. The first element is composed of two literal values, an integer (1) and a quoted string (abcde). The second and third elements also use a quoted string to indicate the second field, but specify the value for the first field with an expression.

Regardless of what method you use to insert values into a collection column, you cannot insert NULL elements into the column. Thus expressions that you use cannot evaluate to NULL. If the collection that you are attempting to insert contains a NULL element, the database server returns an error.

You can also use a collection variable to insert the values of one or more collection elements into a collection column. For more information, see Collection-Derived Table.