MySQL INSERT INTO SELECT Statement

MySQL is an open-source relational database management system that uses Structured Query Language (SQL) to manipulate databases. It stores data in a table format. It provides various statements to perform Create, Read, Update, and Delete operations on a database table.

INSERT INTO SELECT statement is considered as a part of Data Manipulation Language (DML). DML operations deal with the manipulation of data stored in the database. The INSERT INTO SELECT statement in MySQL offers a powerful mechanism for transferring and manipulating data between tables. In this article, we will learn about What INSERT INTO SELECT statement with its syntax and example.

MySQL INSERT INTO SELECT Statement

INSERT INTO SELECT statement is a DML statement and it is used to copy data from one table and inserts it into another table. This statement allows us to copy data from one table and insert it into another table based on specific conditions. It combines features of INSERT INTO and SELECT statements. The data types in the source and target tables must be the same.

Syntax:

INSERT INTO target_table (column1, column2, …)

SELECT column1, column2, …

FROM source_table

WHERE condition

• INSERT INTO target_table (column1, column2, …): This part of the syntax specifies the target table into which you want to insert data. You list the columns (in parentheses) where you want to insert data. The order of the columns should match the order in the subsequent SELECT statement.
• SELECT column1, column2, ..: This is the selection part where you specify the columns you want to retrieve data from in the source table.
• FROM source_table: This specifies the source table from which you are selecting the data.
• WHERE condition: This is an optional clause that allows you to apply a condition to filter the records being selected from the source table. Only the rows that satisfy the specified condition will be inserted into the target table.

Example of INSERT INTO SELECT Statement

Before we start, create the employees table and insert data into the table by using following query.

Query to create an employees table:

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(50),
    department VARCHAR(50),
    salary DECIMAL(10,2)
);

Query to insert some records into a employees table:

INSERT INTO employees (employee_id, employee_name, department, salary)
VALUES
    (1, 'John Doe', 'IT', 60000.00),
    (2, 'Jane Smith', 'HR', 55000.00),
    (3, 'Bob Johnson', 'Finance', 70000.00),
    (4, 'Alice Williams', 'Marketing', 50000.00);

In the below image, there is a table named employees, which has some records.

employee table

Query to create new_employees table:

CREATE TABLE new_employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(50),
    department VARCHAR(50),
    salary DECIMAL(10,2)
);

In the below image, there is a table new_employees, whose structure is same as a employees table, and by using INSERT INTO SELECT statement we are copying data from the thanemployees table to new_employees table whose salary is greater than 55000.

Query:

INSERT INTO new_employees (employee_id, employee_name, department, salary)
SELECT employee_id, employee_name, department, salary
FROM employees
WHERE salary > 55000;

Output:

INSERT INTO SELECT statement

Using SELECT Statement in the VALUES List

SELECT statement in VALUES list allows to insert multiple rows in a single INSERT INTO statement.

In this example, we have to table employees and departments and we will insert data into a new table employee_department_mapping by combining information from both tables.

employees and departments table

When we use VALUES keyword with a subquery, we need to make sure that the subquery returns only one column. In our case, we have two columns employee_id and department_id in each subquery. To address this issue, we can use the SELECT statement directly without the VALUES keyword.

Query:

INSERT INTO employee_department_mapping (employee_id, department_id)
SELECT employee_id, department_id
FROM (
    SELECT employee_id, 1 AS department_id FROM employees WHERE salary > 55000
    UNION ALL
    SELECT employee_id, 2 AS department_id FROM employees WHERE salary <= 55000
    UNION ALL
    SELECT employee_id, 3 AS department_id FROM employees WHERE salary > 60000
) AS subquery;

Output:

employee_department_mapping table

Conclusion

INSERT INTO SELECT statement is a Data Manipulation Language (DML) statement. DML statements are used to perform operations that deals with the manipulation of data stored in the database. INSERT INTO SELECT statement in MySQL allow us to copy data between tables and apply conditions to select specific records. learning INSERT INTO SELECT statement help us to easily manipulate data. The structure of the source and target tables must be same, if not then the INSERT INTO SELECT statement will not work.