Db2 INSERT INTO SELECT

Summary: in this tutorial, you will learn how to use the Db2 INSERT INTO SELECT statement to copy data from a table to another table.

Introduction to Db2 INSERT INTO SELECT statement

The INSERT INTO SELECT statement insert rows returned by a SELECT statement into a table.

The following shows the syntax of the INSERT INTO SELECT statement:

INSERT INTO table_name (column_list)
SELECT-statement;
Code language: SQL (Structured Query Language) (sql)

In this syntax:

• First, specify the name of the target table to which the rows will be inserted and a list of columns.
• Second, use a SELECT statement to query data from another table. The SELECT statement can be any valid query that returns zero or more rows. The number of columns and their data type must match with the column list in the INSERT INTO clause.

We often use the INSERT INTO SELECT statement to copy data from a table to another table; or to insert summary data of a table into another table.

Db2 INSERT INTO SELECT examples

1) Insert rows from a table into another table example

Let’s create a table named simple_lists for the demonstration:

CREATE TABLE simple_lists(
	list_id INT GENERATED BY DEFAULT AS IDENTITY,
	list_name VARCHAR(150) NOT NULL
);Code language: SQL (Structured Query Language) (sql)

The following statement uses the INSERT INTO SELECT statement to insert rows whose values in the list_name column which contains the keyword "Db2" into the simple_lists table:

INSERT INTO 
	simple_lists (list_name) 
SELECT
	list_name
FROM
	lists
WHERE 
	list_name LIKE '%Db2%';
Code language: SQL (Structured Query Language) (sql)

It returned the following message:

2 rows inserted.
Code language: SQL (Structured Query Language) (sql)

It means that two rows have been inserted into the simple_lists table successfully.

To verify data in the simple_list table after insert, you can use the following SELECT statement:

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

2) Insert summary rows from a table into another table

First, create a book_stats that stores the book id, title and the number of authors of the book:

CREATE TABLE book_stats (
	book_id INT NOT NULL PRIMARY KEY,
	title VARCHAR(255) NOT NULL,
	authors INT NOT NULL
);
Code language: SQL (Structured Query Language) (sql)

Second, use the INSERT INTO SELECT to query data from the books and author_books table and insert this summary data into the book_info table:

INSERT INTO 
  book_stats(book_id, title, authors)
SELECT 
	b.book_id,
	b.title, 
	COUNT(a.author_id) author_count
FROM 
	books b
	INNER JOIN book_authors a 
		ON a.book_id = b.book_id
GROUP BY 
  b.book_id, 
  b.title;
Code language: SQL (Structured Query Language) (sql)

Third, verify the data in the book_stats table:

SELECT 
	book_id, 
	title, 
	authors 
FROM 
	book_stats
ORDER BY 
	authors DESC;
Code language: SQL (Structured Query Language) (sql)

The following picture shows the output:

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