INSERT - Inserting rows with SELECT

This syntax of the MySQL statement, INSERT is used to insert rows of data into a table based on the results of a SELECT statement. Values can be given as columns or static values or an expression.

Syntax

Below is the syntax for this command. Text within square brackets (i.e, [ and ] ) are optional. Choices are separated by bars (i.e, | ). Ellipses preceded by a comma indicates a repeating pattern. Ellipses before or after syntax are used to highlight a relevant except from the larger syntax. Text displayed in italic letters represent text that should be replaced with the specific names related to the database (e.g., column with the name of the column).

INSERT [LOW_PRIORITY|HIGH_PRIORITY] [IGNORE]
  [INTO] table [(column,...)]
  SELECT...
  [ON DUPLICATE KEY UPDATE column=expression,...]

Explanation

This method of the MySQL statement, INSERT allows for multiple rows to be inserted in one SQL statement, based on data retrieved from another table by way of a SELECT statement. If no columns are listed, the SELECT will return the values of all columns in the order in which they are in the selected table and will be insert (if possible without error) in the same order in the table designated for inserting data into. If you don't want to retrieve all of the columns of the selected table, or if the columns in both tables are not the same, then you must list the columns to retrieve in the SELECT statement and provided a matching ordered list of the columns of the table that data is to be inserting into.

Examples

For the following example, suppose that the employees table contains a column called softball to indicate whether an employee is a member of the company's softball team. Suppose further that it is decided that a new table should be created to store information about members of the softball team and that the team's captain will have privileges to this new table (softball_team), but no other tables. The employee names and telephone numbers need to be copied into the new table, because the team's captain will not be allowed to do a query on the employees table to extract that information. Here are the MySQL statements to set up the new table with its initial data:

CREATE TABLE softball_team
(player_id INT KEY, player_name VARCHAR(50),  
 position VARCHAR(20), telephone CHAR(8));

INSERT INTO softball_team
(player_id, player_name, telephone)
  SELECT emp_id, CONCAT(name_first, ' ', name_last),
  RIGHT(telephone_home, 8)
  FROM employees
  WHERE softball = 'Y';

The first MySQL statement creates the new table in MySQL. The columns are very simple: a column as an row identifier, one column for both the first and last name of the player, another for the player's home telephone number, and yet another for the player's position, to be filled in later by the team's captain. Normally we wouldn't not include a column like the one for the player's name since that would be duplicating data in two tables. However, the team captain intends to change many of the player's names to a baseball nick name (e.g., Slugger Johnson).

In the second MySQL statement, the INSERT statement uses an embedded SELECT statement to retrieve data from the employees table where the softball column for the row is set to `Y'. The CONCAT() function is used to put together the first and last name, separated by a space. This will go into the name column in the new table. The RIGHT( ) function is used to extract only the last eight characters of the telephone_home column, because all of the employees on the softball team are from the same telephone dialing area. Notice that we've listed the three columns that data is to go into, although there are four in the table. Also notice that the SELECT statement also has three columns of the same data types, but of different names.