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:

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:

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

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