Adding Table Rows using INSERT Statement

The INSERT statement is used to add new rows to an existing table.

Inserting a Row

You can add new rows to a table using the INSERT statement.

Example 10-1 Inserting a single row

sql-> INSERT INTO Users VALUES (10, "John", "Smith", 22, 45000);

Explanation:

This query inserts a row into the Users table. Since you are adding values to all table columns, you do not need to specify column names explicitly.

Output:

{"NumRowsInserted":1}
1 row returned

Using DEFAULT Values

You can insert data into specific columns while leaving others empty by explicitly listing the target column names. For such omitted columns, Oracle NoSQL Database automatically assigns a NULL value or a predefined default value if one was specified during the table creation.

Example 10-2 Using DEFAULT values

sql-> INSERT INTO Users (id, firstname, income) VALUES (11, "Mary", 5000);

Explanation:

In the examples above, since the lastname and age columns exist in the table but their values are not provided in the INSERT statement, Oracle NoSQL Database will default those fields to NULL. If you defined a column with a default (for example, age INTEGER DEFAULT 20) during table creation, the omitted column will take that default value instead of NULL. You must include values for all columns that make up the primary key in your INSERT statement, else the operation will fail.

Output:

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

Using a RETURNING Clause

The RETURNING clause in an INSERT statement allows you to immediately see the data that was just written to the database. This is particularly useful for retrieving values that were automatically generated during INSERT, such as default fields or IDENTITY column values, without needing to run a separate SELECT query.

Example 10-3 Using a RETURNING Clause

sql-> INSERT INTO Users VALUES (18, "Sarah", "Jones", 40, DEFAULT) RETURNING *;

Explanation:

Since we have specified RETURNING *, the values of all the columns are returned.

Output:

{"id":18,"firstname":"Sarah","lastname":"Jones","age":40,"income":null}
1 row returned

Setting a TTL Value

You can use TTL to set an automatic expiration period for a row. Once this time elapses, the row is automatically deleted without any manual intervention. TTL is either specified in days or hours. For more details on TTL, see table creation.

Example 10-4 Setting a TTL value

INSERT INTO Users (id, firstname, income) VALUES (15, "Robert", 7500) SET TTL 2 DAYS;

Explanation:

This query inserts a row that will automatically disappear after 2 days.

Output:

{"NumRowsInserted":1}
1 row 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 for more details.

Here are a few examples on how to use the INSERT statements for the different definitions of an IDENTITY column:

GENERATED ALWAYS AS IDENTITY
GENERATED BY DEFAULT AS IDENTITY
GENERATED BY DEFAULT WITH NULL AS IDENTITY

GENERATED ALWAYS AS IDENTITY

Example 10-5 GENERATED ALWAYS AS IDENTITY - Automatically Generated Values

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

Explanation:

The CREATE statement above creates a table named Employee_test using one column, DeptId, as GENERATED ALWAYS AS IDENTITY. The value of the CACHE attribute specifies the count of sequence numbers that will be generated every time a request is made to the sequence generator. CACHE 1 means the database retrieves and stores only one value at a time. The INSERT statements use the DEFAULT keyword for the DeptId field. This triggers the internal sequence generator to automatically assign unique, incrementing values (for example, 1, 2, and 3) to the employees Sally, Joe, and Dave.

Output:

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

Table 10-1 GENERATED ALWAYS AS IDENTITY

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

In this mode, you cannot provide the DeptId manually.

Example 10-6 GENERATED ALWAYS AS IDENTITY - Manually Entered Values

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

Explanation:

Executing the query causes an exception as you are trying to supply a value (200) manually for the DeptId column.

Output:

Error handling command INSERT INTO Employee_test VALUES (566, 'Jane', 200): Error: at (1, 47) 
Generated always identity column must use DEFAULT construct.

GENERATED BY DEFAULT AS IDENTITY

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.

Example 10-7 GENERATED BY DEFAULT AS IDENTITY

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

You can either let the system generate the ID or provide your own as shown below.

sql-> INSERT INTO Employee_test VALUES (566, 'Jane', 200);
sql-> INSERT INTO Employee_test VALUES (567, ‘Dolly’, DEFAULT);

Explanation:

In the first INSERT query, 200 is inserted as DeptId. In the second query, an ID is automatically generated.

Output:

select * from Employee_test;
{"Empl_id":566,"Name":"Jane","DeptId":200}
{"Empl_id":567,"Name":"Dolly","DeptId":1}
2 rows returned

GENERATED BY DEFAULT ON NULL AS IDENTITY

The behavior is similar to GENERATED BY DEFAULT AS IDENTITY. However, in this case, the system generates a value if you either omit the column or explicitly specify NULL.

Inserting Data into a Child Table

A child table is a table that is in a hierarchical relationship with a parent table. Child tables implicitly inherit the primary key columns of their parent.

Example 10-8 Inserting into a child table

CREATE TABLE Users.UserDetails ( userdetail_id INTEGER, address STRING, email STRING, PRIMARY KEY (userdetail_id));
INSERT INTO Users.UserDetails (id, userdetail_id, address, email) VALUES (1, 100,"Park Avenue", "user@example.com");

Explanation:

The child table UserDetails is referenced using a composite name, and it automatically inherits the primary key columns and shard keys of the parent table Users, so you do not explicitly list the parent's primary key columns in the child table's definition. To insert a row into the child table, you must include the primary key of the parent table Users, which is id.

Output:

SELECT * FROM Users.UserDetails WHERE id = 1;
{"id":1,"userdetail_id":100,"address":"Park Avenue","email":"user@example.com"}
1 row returned

Inserting Rows into a Multi-Region Table

You can insert data into a multi-region table with the MR_COUNTER column. For more details, see Working with Multi-Region Setup and Using MR Counters.

Inserting Rows into a JSON Collection Table

JSON Collection tables are schema-less. When you insert data, a single document is created, which can include any number of JSON fields with valid JSON data types. During insertion, you provide values for the primary key fields along with the other JSON fields in the document. For more details, see Working with JSON Collection Tables.