MySQL INSERT INTO SELECT Query

Introduction

In this chapter, we will learn how to use the INSERT INTO SELECT statement in MySQL. This statement is used to copy data from one table to another. It allows you to insert data into a table by selecting data from another table. This can be useful for transferring data, creating backups, or transforming data. We will cover the syntax, examples, and important considerations for using the INSERT INTO SELECT statement.

INSERT INTO SELECT Query

To insert data into a table by selecting data from another table, we use the INSERT INTO SELECT statement.

Syntax

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

• destination_table: The name of the table where you want to insert data.
• source_table: The name of the table from which you want to select data.
• column1, column2, ..., columnN: The names of the columns to insert and select data.
• condition: The condition to specify which rows to select (optional).

Example

INSERT INTO employees_backup (first_name, last_name, email, hire_date)
SELECT first_name, last_name, email, hire_date FROM employees WHERE hire_date > '2023-07-01';

This example inserts data into the employees_backup table by selecting rows from the employees table where the hire_date is after July 1, 2023.

Inserting Data into a Table with a Different Structure

If the destination table has a different structure from the source table, ensure that the selected columns match the columns in the destination table in terms of data types and order.

Example

INSERT INTO employees_summary (first_name, email)
SELECT first_name, email FROM employees;

This example inserts data into the employees_summary table, which only includes the first_name and email columns from the employees table.

Full Example

Let's go through a full example where we create two tables, insert data into one table, and then use the INSERT INTO SELECT statement to copy data to the other table.

1. Create a Database:

CREATE DATABASE company;

2. Select the Database:

USE company;

3. Create Source Table:

CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    hire_date DATE
);

4. Insert Data into Source Table:

INSERT INTO employees (first_name, last_name, email, hire_date) VALUES
('Rahul', 'Sharma', 'rahul.sharma@example.com', '2023-07-01'),
('Priya', 'Singh', 'priya.singh@example.com', '2023-07-02'),
('Amit', 'Kumar', 'amit.kumar@example.com', '2023-07-03'),
('Neha', 'Verma', 'neha.verma@example.com', '2023-07-04');

5. Create Destination Table:

CREATE TABLE employees_backup (
    id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    hire_date DATE
);

6. INSERT INTO SELECT Query:

INSERT INTO employees_backup (first_name, last_name, email, hire_date)
SELECT first_name, last_name, email, hire_date FROM employees WHERE hire_date > '2023-07-01';

7. Query the Data in the Destination Table:

SELECT * FROM employees_backup;

Output

id	first_name	last_name	email	hire_date
1	Priya	Singh	priya.singh@example.com	2023-07-02
2	Amit	Kumar	amit.kumar@example.com	2023-07-03
3	Neha	Verma	neha.verma@example.com	2023-07-04

Important Considerations

• Data Types: Ensure that the data types of the selected columns match the data types of the columns in the destination table.
• Constraints: Be aware of constraints such as NOT NULL, UNIQUE, and PRIMARY KEY in the destination table that may affect your ability to insert data.
• Performance: Copying large amounts of data can impact performance. Consider using indexing and optimizing your queries to improve performance.

Conclusion

The INSERT INTO SELECT statement is used for copying data from one table to another in MySQL. This chapter covered how to use the INSERT INTO SELECT statement, including examples of copying data between tables with the same structure and with different structures.