How to Insert Data from One Table to Another in MySQL

08/06/2021

Contents

In this article, you will learn how to insert data from one table to another in MySQL.

Inserting data from one table to another in MySQL

Inserting data from one table to another is a common operation in MySQL. This can be done using the INSERT INTO SELECT statement. In this statement, we can specify the columns to be inserted and the source table from which the data is to be taken. Here is a step-by-step guide on how to insert data from one table to another table in MySQL.

Create the target table

First, create the table into which the data will be inserted. You can create the table using the CREATE TABLE statement.

CREATE TABLE target_table (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50),
    age INT,
    email VARCHAR(50)
);

Insert data from the source table into the target table

Next, use the INSERT INTO SELECT statement to insert data from the source table into the target table.

INSERT INTO target_table (name, age, email)
SELECT name, age, email
FROM source_table;

In this example, we are inserting the values from the name, age, and email columns of the source_table into the name, age, and email columns of the target_table. The id column is automatically generated as it is an AUTO_INCREMENT column.

Insert data with a condition

You can also insert data with a condition using the INSERT INTO SELECT statement. For example, you may want to insert only the data that meets a certain condition. Here is an example:

INSERT INTO target_table (name, age, email)
SELECT name, age, email
FROM source_table
WHERE age > 30;

In this example, only the rows with an age greater than 30 will be inserted into the target_table.

Insert data with a join

You can also use a join to insert data from one table into another. Here is an example:

INSERT INTO target_table (name, age, email)
SELECT t.name, t.age, s.email
FROM source_table s
JOIN target_table t
ON s.id = t.id;

In this example, we are inserting the email column from the source_table into the email column of the target_table where the id columns of both tables match.

#MySQL