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 EmployeeTest
(
    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)
);