Open In App

MySQL INSERT INTO SELECT Statement

Last Updated : 18 Jan, 2024
Improve
Improve
Like Article
Like
Save
Share
Report

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

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

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.

employee_and_department_table

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:

select_statement_with_value_list

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.


Previous Article
Next Article

Similar Reads

SQL INSERT INTO SELECT Statement
In SQL, the INSERT INTO statement is used to add or insert records to the specified table. We can use this statement to add data directly to the table. We use the VALUES keyword along with the INSERT INTO statement. VALUES keyword is accompanied by column names in a specific order in which we want to insert values in them. SELECT statement is used
5 min read
MySQL INSERT INTO Statement
In DBMS, CRUD operations (Create, Read, Update, Delete) are fundamental for managing data effectively. Among these, the Create operation, which involves inserting new data into a database, plays a crucial role. In this article, Let's explore how the INSERT INTO statement works and its benefits in the realm of CRUD operations. INSERT INTO StatementT
4 min read
How to Use PL SQL Insert, Update, Delete And Select Statement?
PL/SQL is a powerful extension of SQL specifically designed for Oracle databases. It enables developers to create procedural logic and execute SQL commands within Oracle database environments. In this article, we will explore the usage of fundamental PL/SQL statements such as INSERT, UPDATE, DELETE and SELECT with he help of various examples which
6 min read
Nested Select Statement in MySQL
The relational databases, the ability to retrieve and manipulate data with precision is a cornerstone of effective database management. MySQL, a popular relational database management system, provides a powerful tool called the Nested SELECT statement that empowers developers to perform complex and versatile data queries. So, In this article we wil
5 min read
MySQL SELECT Statement
MySQL SELECT Statement is used to print data from one or more tables. The data returned is stored in the result table, also called as result set. It is one of the most commonly used statements in MySQL. In this article, we will learn about MySQL SELECT statement, from its basic syntax to advanced usage with examples. SELECT Statement in MySQLThe SE
3 min read
How to Call a Stored Procedure Using Select Statement in MySQL?
Stored procedures in MySQL are powerful tools for encapsulating SQL logic and enabling reusability. However, executing stored procedures via SELECT statements can provide additional flexibility, especially when integrating their results directly into queries. This article explores various methods of calling stored procedures using SELECT statements
6 min read
SQL Server SELECT INTO Statement
SQL Server is a relational database management system. SQL Server offers robust security features to protect data integrity and confidentiality. It includes authentication, authorization, encryption, and various mechanisms to secure the database environment. It is designed to scale from small applications to large, enterprise-level databases. It pr
6 min read
SQLite INSERT INTO SELECT
SQLite is a lightweight and server-less relational database management system. It requires very minimal configuration which has proven to be very helpful for developers to integrate it into any applications with ease. Due to its server-less architecture, we can use SQLite in various mobile applications as well as in small desktop applications. In t
4 min read
How to insert request body into a MySQL database using Express js
If you trying to make an API with MySQL and Express JS to insert some data into the database, your search comes to an end. In this article, you are going to explore - how you can insert the request data into MySQL database with a simple Express JS app. Table of Content What is Express JS?What is MySQL?Steps to insert request body in MySQL database
3 min read
SELECT INTO in MySQL
MySQL is an open-source Relational Database Management System that stores data in a structured format using rows and columns. MySQL is a platform-independent language and can be compiled on any platform. MySQL is generally used for storing, manipulating, and retrieving data in RDBMS by using the SQL (Structured Query Language). In this article, we
4 min read