# 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.

Related Topics