MySQL INSERT INTO SELECT Query with Examples

MySQL insert into select query; Through this tutorial, you will learn how to use the MySQL INSERT INTO SELECT Query statement to insert data into a table from the result of a SELECT statement. It’s commonly used to copies the from one table to another table in MySQL.

Here you will learn also, MySQL select into, MySQL insert into table, MySQL insert into select from the same table, MySQL insert values from select.

In the previous tutorial, you learned how to add or delete one or more rows into a database table using the INSERT & DELETE Query statement. Also learn how to use SELECT or UPDATE Query Statement records from the database with MySQL WHERE clause.

How To Use MySQL INSERT INTO SELECT Query

In MySQL – INSERT INTO SELECT statement query commonly used to copies data from one table and inserts it into another table.

Syntax

MySQL INSERT INTO SELECT this syntax, copy all columns from one table to the second table, following syntax:-

 INSERT INTO table2
 SELECT * FROM table1
 WHERE condition;

MySQL INSERT INTO SELECT this syntax, copy some columns(selected column only) from one table to the second table, following syntax:-

 INSERT INTO table2 (column1, column2, column3, …)
 SELECT column1, column2, column3, …
 FROM table1
 WHERE condition;

The INSERT INTO SELECT statement query is very useful when you want to copy data from one table to another table.

INSERT INTO SELECT MySQL Example

Suppose we have the following vendors table with the following structure.

CREATE TABLE vendors(
     id INT AUTO_INCREMENT,
     name VARCHAR(50) NOT NULL,
     phone VARCHAR(50),
     addressLine1 VARCHAR(50),
     addressLine2 VARCHAR(50),
     city VARCHAR(50),
     state VARCHAR(50),
     postalCode VARCHAR(50),
     country VARCHAR(50),
     customer_id INT
     PRIMARY KEY (id)
 );

To create a vendor table, you just need to execute the MySQL query.

Now, you need to include these customers in the Vendor table from the Customer table. The following MySQL statement copies “Vendors” to “Customers”.

INSERT INTO vendors (
     name,
     phone,
     addressLine1,
     addressLine2,
     city,
     state,
     postalCode,
     country,
     customer_id
 )
 SELECT
     name,
     phone,
     addressLine1,
     addressLine2,
     city,
     state ,
     postalCode,
     country,
     customer_id
 FROM
     customers;

The following MySQL statement copies only the UK vendors into “Customers”.

INSERT INTO vendors (
     name,
     phone,
     addressLine1,
     addressLine2,
     city,
     state,
     postalCode,
     country,
     customer_id
 )
 SELECT
     name,
     phone,
     addressLine1,
     addressLine2,
     city,
     state ,
     postalCode,
     country,
     customer_id
 FROM
     customers
 WHERE
     country = 'UK';

After inserting data using the MySQL INSERT INTO SELECT statement query, the following query returns data from the vendors table : –

SELECT * FROM vendors;

Recommended MySQL Tutorials