{"id":7312,"date":"2018-09-01T20:14:39","date_gmt":"2018-09-02T03:14:39","guid":{"rendered":"http:\/\/www.mysqltutorial.org\/?page_id=7312"},"modified":"2024-01-05T01:42:56","modified_gmt":"2024-01-05T08:42:56","slug":"mysql-insert-into-select","status":"publish","type":"page","link":"https:\/\/www.mysqltutorial.org\/mysql-basics\/mysql-insert-into-select\/","title":{"rendered":"MySQL INSERT INTO SELECT"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use the MySQL INSERT INTO SELECT<\/code> statement to insert data into a table, where data comes from the result of a SELECT<\/code> statement.<\/p>\n\n\n\n

Introduction to MySQL INSERT INTO SELECT statement<\/h2>\n\n\n\n

The INSERT statement allows you to insert one or more rows into a table with a list of column values specified in the VALUES<\/code> clause:<\/p>\n\n\n

INSERT<\/span> INTO<\/span> table_name(c1,c2,...)\nVALUES<\/span>(v1,v2,..);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Besides using row values in the VALUES<\/code> clause, you can use the result of a SELECT<\/a><\/code> statement as the data source for the INSERT<\/code> statement.<\/p>\n\n\n\n
The following illustrates the syntax of the INSERT INTO SELECT<\/code> statement:<\/p>\n\n\n
INSERT<\/span> INTO<\/span> table_name(column_list)\nSELECT<\/span> \n   select_list \nFROM<\/span> \n   another_table\nWHERE<\/span>\n   condition;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this syntax, instead of using the VALUES<\/code> clause, you use a SELECT<\/code> statement. The SELECT<\/code> statement may retrieve data from one or more tables.<\/p>\n\n\n\n
Note that the number of columns in the column_list<\/code> and select_list must be equal.<\/p>\n\n\n\n
The INSERT INTO SELECT<\/code> 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.<\/p>\n\n\n\n
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<\/code> and another_table<\/code> in the INSERT INTO ... SELECT<\/code> statement can reference the same table.<\/p>\n\n\n\n
MySQL INSERT INTO SELECT example<\/h2>\n\n\n\n
First, create a new table<\/a> called suppliers<\/code>:<\/p>\n\n\n
CREATE<\/span> TABLE<\/span> suppliers (\n    supplierNumber INT<\/span> AUTO_INCREMENT,\n    supplierName VARCHAR<\/span>(50<\/span>) NOT<\/span> NULL<\/span>,\n    phone VARCHAR<\/span>(50<\/span>),\n    addressLine1 VARCHAR<\/span>(50<\/span>),\n    addressLine2 VARCHAR<\/span>(50<\/span>),\n    city VARCHAR<\/span>(50<\/span>),\n    state VARCHAR<\/span>(50<\/span>),\n    postalCode VARCHAR<\/span>(50<\/span>),\n    country VARCHAR<\/span>(50<\/span>),\n    customerNumber INT<\/span>,\n    PRIMARY KEY<\/span> (supplierNumber)\n);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Suppose all customers in California, USA<\/code> become the company’s suppliers. The following query finds all customers who are located in California, USA<\/code>:<\/p>\n\n\n
SELECT<\/span> \n    customerNumber,\n    customerName,\n    phone,\n    addressLine1,\n    addressLine2,\n    city,\n    state,\n    postalCode,\n    country\nFROM<\/span>\n    customers\nWHERE<\/span>\n    country = 'USA'<\/span> AND<\/span> \n    state = 'CA'<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"MySQL<\/figure>\n\n\n\n
Second, insert customers who are located in California USA<\/code> from the customers<\/code> table\u00a0into the\u00a0suppliers<\/code> table using the INSERT INTO SELECT<\/code> statement: <\/p>\n\n\n
INSERT<\/span> INTO<\/span> suppliers (\n    supplierName, \n    phone, \n    addressLine1,\n    addressLine2,\n    city,\n    state,\n    postalCode,\n    country,\n    customerNumber\n)\nSELECT<\/span> \n    customerName,\n    phone,\n    addressLine1,\n    addressLine2,\n    city,\n    state ,\n    postalCode,\n    country,\n    customerNumber\nFROM<\/span> \n    customers\nWHERE<\/span> \n    country = 'USA'<\/span> AND<\/span> \n    state = 'CA'<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
It returned the following message indicating that 11 rows had been inserted successfully.<\/p>\n\n\n
11 row(s) affected Records: 11  Duplicates: 0  Warnings: 0<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Third, verify the insert by querying data from the suppliers<\/code> table:<\/p>\n\n\n
SELECT<\/span> * FROM<\/span> suppliers;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Here is the output:<\/p>\n\n\n\n
\"mysql<\/figure>\n\n\n\n
Using SELECT statement in the VALUES list<\/h2>\n\n\n\n
First, create a new table<\/a> called stats<\/code>:<\/p>\n\n\n
CREATE<\/span> TABLE<\/span> stats (\n    totalProduct INT<\/span>,\n    totalCustomer INT<\/span>,\n    totalOrder INT<\/span>\n);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
INSERT<\/span> INTO<\/span> stats(totalProduct, totalCustomer, totalOrder)\nVALUES<\/span>(\n\t(SELECT<\/span> COUNT<\/span>(*) FROM<\/span> products),\n\t(SELECT<\/span> COUNT<\/span>(*) FROM<\/span> customers),\n\t(SELECT<\/span> COUNT<\/span>(*) FROM<\/span> orders)\n);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this example:<\/p>\n\n\n\n
Second, use the INSERT<\/a><\/code> statement to insert values that come from the SELECT<\/code> statements:<\/p>\n\n\n