MySQL Insert Into with Select Query

MySQL insert into select query; Through this tutorial, i am going to show you how to use the MySQL INSERT INTO SELECT Query statement to insert data into a table from the result of a SELECT statement.

MySQL Insert Into with Select Query

MySQL INSERT INTO SELECT statement query is used to copies data from one table and inserts it into another table in same query.

Syntax of MySQL Insert Into with Select Query

The basic syntax of MySQL Insert Into with Select Query; as follows:

 INSERT INTO table2
 SELECT * FROM table1
 WHERE condition;

Example of MySQL INSERT INTO SELECT this syntax, copy some columns(selected column only) from one table to the second table; as follows:

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

Example 1 – MySQL INSERT INTO SELECT MySQL

The INSERT INTO SELECT statement query is very useful when you want to copy data from one table to another table; As follows:

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