MySQL INSERT INTO SELECT
Summary: in this tutorial, you will learn how to use the MySQL INSERT INTO SELECT statement to insert data into a table, where data comes from the result of a SELECT statement.
MySQL INSERT INTO SELECT Overview
In the previous tutorial, you learned how to insert one or more rows into a table using the INSERT statement with a list of column values specified in the VALUES clause.
INSERT INTO table_name(c1,c2,...)
VALUES(v1,v2,..);Code language: SQL (Structured Query Language) (sql)
Besides using row values in the VALUES clause, you can use the result of a SELECT statement as the data source for the INSERT statement.
The following illustrates the syntax of the INSERT INTO SELECT statement:
INSERT INTO table_name(column_list)
SELECT
select_list
FROM
another_table
WHERE
condition;Code language: SQL (Structured Query Language) (sql)
In this syntax, instead of using the VALUES clause, you can use a SELECT statement. The SELECT statement can retrieve data from one or more tables.
The INSERT INTO SELECT statement is very useful when you want to copy data from other tables to a table or to summary data from multiple tables into a table.
MySQL INSERT INTO SELECT example
First, create a new table calledsuppliers :
CREATE TABLE suppliers (
supplierNumber INT AUTO_INCREMENT,
supplierName 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),
customerNumber INT,
PRIMARY KEY (supplierNumber)
);Code language: SQL (Structured Query Language) (sql)
Note that you will learn how to create a new table in the subsequent tutorial. For now, you just need to execute this statement to create the suppliers table.
Suppose all customers from California, USA become the company’s suppliers. The following query finds all customers who locate in California, USA:
SELECT
customerNumber,
customerName,
phone,
addressLine1,
addressLine2,
city,
state,
postalCode,
country
FROM
customers
WHERE
country = 'USA' AND
state = 'CA';Code language: SQL (Structured Query Language) (sql)
Second, use the INSERT INTO ... SELECT statement to insert customers who locate in California USA from the customers table into the suppliers table:
INSERT INTO suppliers (
supplierName,
phone,
addressLine1,
addressLine2,
city,
state,
postalCode,
country,
customerNumber
)
SELECT
customerName,
phone,
addressLine1,
addressLine2,
city,
state ,
postalCode,
country,
customerNumber
FROM
customers
WHERE
country = 'USA' AND
state = 'CA';Code language: SQL (Structured Query Language) (sql)
It returned the following message indicating that 11 rows have been inserted successfully.
11 row(s) affected Records: 11 Duplicates: 0 Warnings: 0Code language: SQL (Structured Query Language) (sql)
Third, verify the insert by querying data from the suppliers table:
SELECT * FROM suppliers;Code language: SQL (Structured Query Language) (sql)
Here is the output:
Using SELECT statement in the VALUES list
First, create a new table called stats:
CREATE TABLE stats (
totalProduct INT,
totalCustomer INT,
totalOrder INT
);Code language: SQL (Structured Query Language) (sql)
Second, use the INSERT statement to insert values that come from the SELECT statements:
INSERT INTO stats(totalProduct, totalCustomer, totalOrder)
VALUES(
(SELECT COUNT(*) FROM products),
(SELECT COUNT(*) FROM customers),
(SELECT COUNT(*) FROM orders)
);Code language: SQL (Structured Query Language) (sql)
In this example:
- First, use the
SELECTstatements with theCOUNT()functions to get the total products, employees, and orders. - Second, use the values returned from the
SELECTstatement in place of values in theVALUESclause of theINSERTstatement.
Third, query data from the tablestats:
SELECT * FROM stats;Code language: SQL (Structured Query Language) (sql)
In this tutorial, you have learned how to use the MySQL INSERT INTO SELECT statement to insert data into a table from a result set.