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.

Introduction to MySQL INSERT INTO SELECT statement

The INSERT statement allows you to insert one or more rows into a table 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 use a SELECT statement. The SELECT statement may retrieve data from one or more tables.

Note that the number of columns in the column_list and select_list must be equal.

The INSERT INTO SELECT statement can very useful when you want to copy data from other tables to a table or to summarize data from multiple tables into a table.

Please note that it’s possible to select rows in a table and insert them into the same table. In other words, the table_name and another_table in the INSERT INTO ... SELECT statement can reference the same table.

MySQL INSERT INTO SELECT example

First, create a new table called suppliers:

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)

Suppose all customers in California, USA become the company’s suppliers. The following query finds all customers who are located 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, insert customers who are located in California USA from the customers table into the suppliers table using the INSERT INTO SELECT statement:

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 had 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 SELECT statements with the COUNT() functions to get the total products, employees, and orders.
• Second, use the values returned from the SELECT statement in place of values in the VALUES clause of the INSERT statement.

Third, query data from the table stats:

SELECT * FROM stats;Code language: SQL (Structured Query Language) (sql)

Summary

• Use the MySQL INSERT INTO SELECT statement to insert data into a table from a result set.