Previous Page
Next Page

Inserting Retrieved Data

INSERT is usually used to add a row to a table using specified values. There is another form of INSERT that can be used to insert the result of a SELECT statement into a table. This is known as INSERT SELECT, and, as its name suggests, it is made up of an INSERT statement and a SELECT statement.

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:

Note

Instructions Needed for the Next Example 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. The format of the custnew table should be the same as the customers table described in Appendix B, "The Example Tables." 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.


• Input

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;

• Analysis

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.

Tip

Column Names in INSERT SELECT 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.

Note

More Examples Looking for more examples of INSERT use? See the example table population scripts (described in Appendix B) used to create the example tables used in this book.



Previous Page
Next Page