Inserting Values From a SELECT Statement into Another Table

1. Introduction

In SQL, we often need to move data between tables or insert results from a query into a new or existing table. This task is commonly achieved through the INSERT INTO … SELECT statement, which allows us to copy data to another based on a query efficiently.

In this tutorial, we’ll discuss using the SELECT statement to insert values to the target table.

2. Setup

For demo purposes, we’ll use the Baeldung University schema. We’ll use the registration table as the source table and write the query result to target table registration_analytics.

3. Inserting Values From SELECT Statement

Let’s discuss the syntax for inserting data from a SELECT statement into another table:

INSERT INTO target_table(column1, column2, ...)
SELECT column1, column2
FROM source_table
WHERE condition;

In the above query, INSERT INTO target_table  specifies the target_table and the columns into which the data will be inserted. Meanwhile, the SELECT statement returns the column list for the target table.

To clarify, let’s look at an example. As mentioned in the setup section earlier, we have two tables registration and registration_analytics. In this case, we’ll query the registration table to get the number of courses a student enrolled in for a given year, and we’ll write the result into the registration_analytics table:

INSERT INTO registration_analytics(student_id, number_of_course_enrolled, year)
SELECT student_id, COUNT(course_id) AS number_of_course_enrolled, year
FROM registration
GROUP BY student_id, year;

To verify the result, let’s execute the query and review the output:

university=# SELECT * FROM registration_analytics;
 id | student_id | number_of_course_enrolled | year
----+------------+---------------------------+------
 13 |       1001 |                         3 | 2021
 14 |       1007 |                         3 | 2022
 15 |       1003 |                         3 | 2022

As we can see, our query writes aggregated results for the number_of_course_enrolled by the student each year to the registration_analytics table.

4. Conclusion

In this article, we discussed and wrote a query about copying data between tables with the INSERT INTO … SELECT statement.  The INSERT INTO … SELECT statement is a powerful tool in SQL for transferring and manipulating data between tables.