Db2 INSERT

Summary: in this tutorial, you will learn how to use the Db2 INSERT statement to insert a row into a table.

Introduction to Db2 INSERT statement

To insert a new row into a table, you use the INSERT statement. The following shows the syntax of the INSERT statement:

INSERT INTO 
	table_name (column_list)
VALUES 
	(value_list);
Code language: SQL (Structured Query Language) (sql)

In this syntax:

• First, specify the name of the table to which you want to insert a new row after the INSERT INTO keywords followed by comma-separated column list enclosed in parentheses.
• Then, specify the comma-list of values after the VALUES keyword. The values list is also surrounded by parentheses. The order of values in the value list must be corresponding to the order of columns in the column list.

If you don’t specify a column of a table in the column list, you must ensure that Db2 can provide a value for insertion or an error will occur.

Db2 automatically uses the following value for the column that exists in the table but does not specify in the column list of the INSERT statement:

• The next incremental value if the column is an identity column.
• The default value if the column has a specified default value.
• The NULL if the column is defined as a nullable column.
• The computed value if the column is a generated column.

Db2 INSERT statement examples

The following statement creates a new table named lists for the demonstration:

CREATE TABLE lists(
	list_id INT GENERATED BY DEFAULT AS IDENTITY NOT NULL, 
	list_name VARCHAR(150) NOT NULL,
	description VARCHAR(255),
	created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
Code language: SQL (Structured Query Language) (sql)

The lists table has four columns:

• list_id is an identity column so that Db2 will provide a sequential integer if you don’t supply a value.
• list_name is a varying character column with NOT NULL constraint. The INSERT statement must include this column.
• description is also a varying character column. However, it is a nullable column which uses NULL as a default value.
• created_at column is a TIMESTAMP column with a default value of the current timestamp of the operating system on which Db2 instance runs.

1) Basic INSERT example

The following example uses the INSERT statement to insert a new row into the lists table:

INSERT INTO lists(list_name)
VALUES('Daily');
Code language: SQL (Structured Query Language) (sql)

Here is the output:

1 row inserted.

In this INSERT statement:

• The identity column list_id will take a default auto-increment integer.
• The description column took null as a default.
• The created_at column get the current timestamp at the time of insert.

After inserting the row into the lists table, you can use the following SELECT statement to view what the modified table looks like:

SELECT 
	list_id, 
	list_name, 
	created_at
FROM 
	lists;
Code language: SQL (Structured Query Language) (sql)

Here is the output:

2) Inserting a new and return id example

To return the generated id of the identity column after insert, you use the SELECT FROM INSERT statement:

SELECT 
	list_id
FROM FINAL TABLE
(INSERT INTO lists(list_name)
VALUES('Weekly Digest'));
Code language: SQL (Structured Query Language) (sql)

The SELECT FROM INSERT also allows you to get multiple generated values, for example:

SELECT 
   list_id, 
   list_name, 
   created_at 
FROM FINAL TABLE (
   INSERT INTO lists(list_name) 
   VALUES('Bi-Weekly Digest')
);Code language: SQL (Structured Query Language) (sql)

3) Inserting default values example

If you specify the column that has a default value in the column list of the INSERT statement, you can use the DEFAULT keyword in the value list.

This example uses DEFAULT in the INSERT statement to insert a new row into the lists table:

INSERT INTO lists(list_name, created_at)
VALUES('Monthly Digest', DEFAULT);
Code language: SQL (Structured Query Language) (sql)

In this example, we used the DEFAULT keyword so Db2 uses the default value of the created_at column to insert.

4) Inserting values into the identity column example

Typically, you don’t need to specify a value for the identity column when you insert a new row into the table because Db2 will provide the value.

However, in some situations such as data migration, you may want to insert a value into the identity column:

See the following INSERT statement:

INSERT INTO lists(list_id, list_name)
VALUES(5,'Special Topics');Code language: SQL (Structured Query Language) (sql)

The statement works fine because the list_id is declared with the option GENERATED BY DEFAULT AS IDENTITY NOT NULL.

If you declare the list_id column as GENERATED ALWAYS AS IDENTITY NOT NULL, then you will not able to insert a value into this column.

In this tutorial, you have learned how to use the Db2 INSERT statement to insert a new row into a table.