ToC path:

SQL Insert

     
    INSERT [OR UPDATE] INTO table [(fields-list)] insert-data
    fields-list::= field {, field }
    field::= identifier { . identifier }
    insert-data::= VALUES expressions-list | select-statement | record-data
    expressions-list::= ( expression { , expression } )
     
The SQL INSERT statement creates a new row in the specified table, with the values indicated by the insert-data, which can be a list of values, the result of a SELECT statement, or a C structure containing the data to be inserted. If a fields-list is omitted, the application must provide a value in the expressions-list for every field in the table, in the same order in which the fields were defined in the database schema.

For example, given the table definition above, the following statements are equivalent:
     
    INSERT INTO t VALUES (1, 2, 3, 4, 5)
    INSERT INTO t (A, B, C, D, E) VALUES (1, 2, 3, 4, 5)
     
If a fields-list is provided, then fields that are not specified in the fields-list will take on the default value specified in the database schema, or the system-defined default for the field type (e.g. zero, or an empty string).

The result set of a SQL SELECT statement used in lieu of an expressions-list must return exactly the same number of fields as the table if no fields-list is specified, or the same number of fields specified in the fields-list if one is provided.

For example,
     
    INSERT INTO t SELECT V, W, X, Y, Z from t2
    Or
    INSERT INTO t (A, C, E) SELECT V, X, Z from t2
     
The eXtremeSQL-specific “record-data” form of the INSERT statement allows the application to, instead of providing a “fields-list”, specify the address of a C structure containing the field values to be inserted. The C structure must correspond exactly to the table as defined in the database schema.

For example, the following method call uses the data supplied in structure person to create a new row in table Person:
     
    engine.executeStatement( “INSERT INTO Person %r”, &person );
     
Insert or update

The insert or update statement searches first for an object having the unique key value(s) specified in the values clause. If found the object will be updated; if not found a new object will be created. Note that it is mandatory that the first column(s) in the statement are primary keys to locate the object.

This statement can be applied to objects with any type of field and is especially useful with fields of type sequence. For sequences and vectors this statement can be used to insert new values or append values to existing fields. To append values the append_mode must be set to true, which is the default. Otherwise the vector or sequence values will be overwritten. The append_mode parameter is modified via the SQL set command:
     
    set append_mode true
     
For example the following statement would create a simple table and populate the sequence field with 3 values:
     
    create table t(id string primary key, day sequence(unsigned(4) asc));
    insert into t values(1, [1,2,3]);
     
The following statement would append 3 values to the existing object with key=1:
     
    insert or update into t values(1, [4,5,6]);
     
The following statements would overwrite the existing sequence with new values:
     
    set append_mode false
    insert or update into t values(1, [7,8,9]);
     
Bulk insert

Remote execution of multiple INSERTs can be optimized.