Suppose you want to merge a list of customers from another table into your customers table. Instead of reading one row at a time and inserting it with INSERT, you can do the following:
The following example imports data from a table named custnew into the customers table. To try this example, create and populate the custnew table first. When populating custnew, be sure not to use cust_id values that were already used in customers (the subsequent INSERT operation will fail if primary key values are duplicated), or just omit that column and have MySQL generate new values during the import process.
INSERT INTO customers(cust_id,
cust_contact,
cust_email,
cust_name,
cust_address,
cust_city,
cust_state,
cust_zip,
cust_country)
SELECT cust_id,
cust_contact,
cust_email,
cust_name,
cust_address,
cust_city,
cust_state,
cust_zip,
cust_country
FROM custnew;
This example uses INSERT SELECT to import all the data from custnew into customers. Instead of listing the VALUES to be inserted, the SELECT statement retrieves them from custnew. Each column in the SELECT corresponds to a column in the specified columns list. How many rows will this statement insert? That depends on how many rows are in the custnew table. If the table is empty, no rows will be inserted (and no error will be generated because the operation is still valid). If the table does, in fact, contain data, all that data is inserted into customers.
This example imports cust_id (and assumes that you have ensured that cust_id values are not duplicated). You could also simply omit that column (from both the INSERT and the SELECT) so MySQL would generate new values.
This example uses the same column names in both the INSERT and SELECT statements for simplicity's sake. But there is no requirement that the column names match. In fact, MySQL does not even pay attention to the column names returned by the SELECT. Rather, the column position is used, so the first column in the SELECT (regardless of its name) will be used to populate the first specified table column, and so on. This is very useful when importing data from tables that use different column names.
The SELECT statement used in an INSERT SELECT can include a WHERE clause to filter the data to be inserted.
In this tutorial, you learned how to use INSERT to insert rows into a database table. You learned several other ways to use INSERT, and why explicit column specification is preferred. You also learned how to use INSERT SELECT to import rows from another table. In the next tutorial, you'll learn how to use UPDATE and DELETE to further manipulate table data.
