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
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
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
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.
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
DELETE to further manipulate table data.