Db2 INSERT Multiple Rows

Summary: in this tutorial, you will learn how to insert multiple rows into a table by using a single INSERT statement.

Db2 INSERT multiple rows statement overview

The Db2 INSERT statement allows you to insert multiple rows into a table using the following syntax:

INSERT INTO table_name(column_list) VALUES (value_list_1), (value_list_2), (value_list_3), ...;
Code language: SQL (Structured Query Language) (sql)

To insert multiple rows into a table, you need to:

  • First, specify the name of the table and a list of columns in parentheses.
  • Second, use a list of comma-separated lists of column values. Each item in the list represents a row that will be inserted into the table.

DB2 INSERT multiple rows example

We will use the lists table created in the insert tutorial.

1) Inserting multiple rows into a table example

The following statement inserts three rows into the lists table:

INSERT INTO lists(list_name) VALUES ('Database Weekly'), ('Db2 Weekly'), ('Db2 Insights');
Code language: SQL (Structured Query Language) (sql)

To verify the data in the lists table after inserting, you can use the following query:

SELECT * FROM lists;
Code language: SQL (Structured Query Language) (sql)

Here is the output:

Db2 Insert Multiple Rows example

2) Inserting multiple rows into a table and return a list of ids

To insert multiple rows into the lists table and return a list of inserted ids, you use the following statement:

SELECT list_id FROM FINAL TABLE ( INSERT INTO lists(list_name) VALUES ('BigData Digest'), ('Data Warehouse Solutions') );
Code language: SQL (Structured Query Language) (sql)

The statement returns the following output:

Db2 Insert Multiple Rows and return id list example

The following SELECT statement verifies the data of the lists table after insert:

Db2 Insert Multiple Rows example output

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

Was this tutorial helpful ?