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.

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:

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.