Previous Next JavaScript must be enabled to correctly display this content

## Adding Table Rows using INSERT and UPSERT

This topic provides examples on how to add table rows using the SQL for Oracle NoSQL Database INSERT and UPSERT statements.

You use the INSERT statement to insert or update a single row in an existing table.

Examples:

If you executed the SQLBasicExamples Script, you should already have created the table named `Users`. The table had this definition:

    CREATE TABLE Users
    (
    	id integer,
    	firstname string,
    	lastname string,
    	age integer,
    	income integer,
    	primary key (id)
    );
    sql-> describe table Users;
     === Information ===
     +-------+-----+-------+----------+----------+--------+----------+---------+-------------+
     | name  | ttl | owner | sysTable | r2compat | parent | children | indexes | description |
     +-------+-----+-------+----------+----------+--------+----------+---------+-------------+
     | Users |     |       | N        | N        |        |          |         |             |
     +-------+-----+-------+----------+----------+--------+----------+---------+-------------+

     === Fields ===
     +----+-----------+---------+----------+-----------+----------+------------+----------+
     | id |   name    |  type   | nullable |  default  | shardKey | primaryKey | identity |
     +----+-----------+---------+----------+-----------+----------+------------+----------+
     |  1 | id        | Integer | N        | NullValue | Y        | Y          |          |
     +----+-----------+---------+----------+-----------+----------+------------+----------+
     |  2 | firstname | String  | Y        | NullValue |          |            |          |
     +----+-----------+---------+----------+-----------+----------+------------+----------+
     |  3 | lastname  | String  | Y        | NullValue |          |            |          |
     +----+-----------+---------+----------+-----------+----------+------------+----------+
     |  4 | age       | Integer | Y        | NullValue |          |            |          |
     +----+-----------+---------+----------+-----------+----------+------------+----------+
     |  5 | income    | Integer | Y        | NullValue |          |            |          |
     +----+-----------+---------+----------+-----------+----------+------------+----------+

To insert a new row into the `Users` table, use the `INSERT` statement as follows. Because you are adding values to all table columns, you do not need to specify column names explicitly:

    sql-> INSERT INTO Users VALUES (10, "John", "Smith", 22, 45000);
    {"NumRowsInserted":1}
    1 row returned
    sql-> select * from Users;
    {"id":10,"firstname":"John","lastname":"Smith","age":22,"income":45000}

To insert data into some, but not all, table columns, specify the column names explicitly in the INSERT statement. Any columns that you do not specify are assigned either `NULL` or the default value supplied when you created the table:

    sql-> INSERT INTO Users (id, firstname, income)
    VALUES (11, "Mary", 5000);
    {"NumRowsInserted":1}
    1 row returned

    sql-> select * from Users;
    {"id":11,"firstname":"Mary","lastname":null,"age":null,"income":5000}
    {"id":10,"firstname":"John","lastname":"Smith","age":22,"income":45000}
    2 rows returned

Using the UPSERT Statement

The word `UPSERT` combines `UPDATE` and `INSERT`, describing it statement's function. Use an `UPSERT` statement to insert a row where it does not exist, or to update the row with new values when it does.

For example, if you already inserted a new row as described in the previous section, executing the next statement updates user John’s age to 27, and income to 60,000. If you did not execute the previous `INSERT` statement, the `UPSERT` statement inserts a new row with user id 10 to the `Users` table.

    sql-> UPSERT INTO Users VALUES (10, "John", "Smith", 27, 60000);
    {"NumRowsInserted":0}
    1 row returned
    sql-> UPSERT INTO Users VALUES (11, "Mary", "Brown", 28, 70000);
    {"NumRowsInserted":0}
    1 row returned

    sql-> select * from Users;
    {"id":10,"firstname":"John","lastname":"Smith","age":22,"income":60000}
    {"id":11,"firstname":"Mary","lastname":"Brown","age":28,"income":70000}
    2 rows returned

Using an IDENTITY Column

You can use IDENTITY columns to automatically generate values for a table column each time you insert a new table row. See Identity Column in the SQL Reference Guide.

Here are a few examples for how to use the INSERT statements for both flavors of an IDENTITY column:

- GENERATED ALWAYS AS IDENTITY

- GENERATED BY DEFAULT [ON NULL] AS IDENTITY

Create a table named `Employee_test` using one column, DeptId, as GENERATED ALWAYS AS IDENTITY. This IDENTITY column is not the primary key. Insert a few rows into the table.

    sql-> CREATE TABLE Employee_test
    (
        Empl_id INTEGER,
        Name STRING,
        DeptId INTEGER GENERATED ALWAYS AS IDENTITY (CACHE 1),
        PRIMARY KEY(Empl_id)
    );

    INSERT INTO Employee_test VALUES (148, 'Sally', DEFAULT);
    INSERT INTO Employee_test VALUES (250, 'Joe', DEFAULT);
    INSERT INTO Employee_test VALUES (346, 'Dave', DEFAULT);

The INSERT statement inserts the following rows with the system generates values 1, 2, and 3 for the IDENTITY column `DeptId`.

Empl_id | Name | DeptId
--- | --- | ---
148 | Sally | 1
250 | Joe | 2
346 | Dave | 3

You cannot specify a value for the `DeptId` IDENTITY column when inserting a row to the `Employee_test` table, because you defined that column as `GENERATED ALWAYS AS IDENTITY`. Specifying DEFAULT as the column value, the system generates the next IDENTITY value. Conversely, trying to execute the following SQL statement causes an exception, because you supply a value (200) for the `DeptId` column.

    sql-> INSERT INTO Employee_test VALUES (566, 'Jane', 200);

If you create the column as `GENERATED BY DEFAULT AS IDENTITY` for the `Employee_test` table, the system generates a value only if you fail to supply one. For example, if you define the `Employee_test` table as follows, then execute the INSERT statement as above, the statement inserts the value 200 for the employee’s `DeptId` column.

    CREATE Table Employee_test
    (
        Empl_id INTEGER,
        Name STRING,
        DeptId INTEGER GENERATED BY DEFAULT AS IDENTITY (CACHE 1),
        PRIMARY KEY(Empl_id)
    );