MySQL INSERT INTO SELECT

The statement INSERT INTO SELECT copies data from one table and inserts it into another table.

• The source and target table must have the same data type.
• Existing records in the target table are not affected.

[st_adsense]  

Syntax:

The following syntax, copy all columns from table t1 to table t2.

INSERT INTO t2
SELECT * FROM t1
WHERE condition;

The following syntax, copy only the selected columns from table t1 to table t2.

 INSERT INTO t2 (column1, column2, column3, …)
 SELECT column1, column2, column3, …
 FROM t1
 WHERE condition;

 

Example :

Suppose we have the following table “Suppliers” with the following structure.

CREATE TABLE Suppliers (
     id INT AUTO_INCREMENT,
     name VARCHAR(50) NOT NULL,
     phone_number VARCHAR(50),
     address VARCHAR(50),
     country VARCHAR(50),
     customer_id INT,
     PRIMARY KEY (id)
 );

[st_adsense]  
Suppose we have the following “Customers” table (We will use it to fill the “Suppliers” table).

> SELECT * FROM Customers;

+-------------+--------+--------------+-------------------------+-----------------+
| customer_id |  name  | phone_number |        address          |      email      |
+-------------+--------+--------------+-------------------------+-----------------+
|         101 |  Alex  |  0214564785  | 819 Saint Francis       | alex1@gmail.com |
|         102 |  Alex  |  0325145698  | 285 Java Lane Missouri  | alex2@gmail.com |
|         103 |  Alex  |  0452145874  | 188 Clay Street Indiana | alex3@gmail.com |
|         104 |  Emily |  0752645874  | 171 Jarvisville Road    | emily@gmail.com |
|         105 |  Jean  |  0052145874  | 188 Clay Street Indiana | jean@gmail.com  |
|         106 |  Bob   |  0152145874  | 285 Java Lane Missouri  | bob1@gmail.com  |
|         107 |  Bob   |  0952145874  | 819 Saint Francis Way   | bob2@gmail.com  |
+-------------+--------+--------------+-------------------------+-----------------+

Now we will fill in the “Suppliers” table from the “Customers” table.

 INSERT INTO Suppliers (
     name, 
     phone_number, 
     address,
     country,
     customer_id
 )
 SELECT 
     name, 
     phone_number, 
     address,
     country,
     customer_id
 FROM 
     Customers;

The following query copies only the customers from ‘US’ into the “suppliers” table.

 INSERT INTO Suppliers (
     name, 
     phone_number, 
     address,
     country,
     customer_id
 )
 SELECT 
     name, 
     phone_number, 
     address,
     email,
     customer_id
 FROM 
     Customers;
 WHERE 
     country = 'US';

[st_adsense]  
After inserting data using INSERT INTO SELECT query, the following query returns the data from the “Suppliers” table.

SELECT * FROM Suppliers;

> SELECT * FROM Suppliers;

+-------------+--------+--------------+-------------------------+-----------------+
| customer_id |  name  | phone_number |        address          |      email      |
+-------------+--------+--------------+-------------------------+-----------------+
|         101 |  Alex  |  0214564785  | 819 Saint Francis       | alex1@gmail.com |
|         102 |  Alex  |  0325145698  | 285 Java Lane Missouri  | alex2@gmail.com |
|         103 |  Alex  |  0452145874  | 188 Clay Street Indiana | alex3@gmail.com |
|         104 |  Emily |  0752645874  | 171 Jarvisville Road    | emily@gmail.com |
|         105 |  Jean  |  0052145874  | 188 Clay Street Indiana | jean@gmail.com  |
|         106 |  Bob   |  0152145874  | 285 Java Lane Missouri  | bob1@gmail.com  |
|         107 |  Bob   |  0952145874  | 819 Saint Francis Way   | bob2@gmail.com  |
+-------------+--------+--------------+-------------------------+-----------------+