How To Use MySQL INSERT INTO SELECT Query

In MySQL Tutorial Point, Here 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.

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.

INSERT INTO SELECT MySQL

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 these syntax, copy all columns from one table to second table, following syntax :-

 INSERT INTO table2
SELECT * FROM table1
WHERE condition;

MySQL INSERT INTO SELECT these syntax, copy some columns(selected column only) from one table to 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;
Spread the love

Be First to Comment

Leave a Reply

Your email address will not be published. Required fields are marked *