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.
Using the AT Clause (ESQL/C, SPL)

Using the AT Clause (ESQL/C, SPL)

Use the AT clause to insert LIST elements at a specified position in a collection variable. By default, HCL OneDB™ adds a new element at the end of a LIST collection.

If you specify a position greater than the number of elements in the list, the database server adds the element to the end of the list. You must specify a position value of at least 1 because the first element in the list is at position 1.

The following SPL example inserts a value at a specific position in a list:

CREATE PROCEDURE test3()
   DEFINE a_list LIST(SMALLINT NOT NULL);
   SELECT list_col INTO a_list FROM table1 WHERE id = 201;
   INSERT AT 3 INTO TABLE(a_list) VALUES( 9 );
   UPDATE table1 VALUES list_col = a_list WHERE id = 201;
END PROCEDURE;

Suppose that before this INSERT, a_list contained the elements {1,8,4,5,2}. After this INSERT, a_list contains the elements {1,8,9,4,5,2}. The new element 9 was inserted at position 3 in the list. For more information on inserting values into collection variables, see Collection-Derived Table.