SQL INSERT INTO SELECT Statement

The SQL INSERT INTO SELECT statement copies data from one table to another table.

The INSERT INTO SELECT statement requires that data types in source and target tables match.

The INSERT INTO SELECT statement will not affect already existing data in the target table.

Syntax

The syntax for the INSERT INTO SELECT statement can be given as follows:

• To copy all columns from the source table into the target table, use the following syntax:

  INSERT INTO table_target
  SELECT * FROM table_source
  WHERE condition; 
  

• To copy only some columns from the source table into the target table, use the following syntax:

  INSERT INTO table_target (column1, column2, ... columnN)
  SELECT column1, column2, ... columnN
  FROM table_source
  WHERE condition; 
  

Demo table

Let us consider the following "Client" and "Supplier" tables :

• Client table:

  +----+-----------------+----------------+-------------+---------+
  | id | name            | contact_name   | city        | country |
  +----+-----------------+----------------+-------------+---------+
  | 1  | Maria Martinez  | Maria Martinez | Boston      | USA     |
  | 2  | Donna Williams  | James Williams | Seattle     | USA     |
  | 3  | James Smith     | Andrew Smith   | Los Angeles | USA     |
  | 4  | Jennifer Garcia | Charles Garcia | Las Vegas   | USA     |
  +----+-----------------+----------------+-------------+---------+
  

• Supplier table:

  +----+-----------------+-----------------+---------------+---------+
  | id | supplier_name   | contact_name    | city          | country |
  +----+-----------------+-----------------+---------------+---------+
  | 1  | Google          | Jane Jackson    | Mountain View | USA     |
  | 2  | Microsoft       | Samantha Garcia | Seattle       | USA     |
  | 3  | Electronic Arts | Joe Smith       | Redwood City  | USA     |
  | 4  | Tyson Foods     | Derek Anderson  | Springdale    | USA     |
  +----+-----------------+-----------------+---------------+---------+
  

SQL INSERT INTO SELECT Examples

The following SQL statement copies data from the "Supplier" table into the "Client" table.

INSERT INTO client (name, contact_name, city, country)
SELECT supplier_name, contact_name, city, country 
FROM supplier;

After the execution, the output of the "Client" table will be as follows:

+----+-----------------+-----------------+---------------+---------+
| id | name            | contact_name    | city          | country |
+----+-----------------+-----------------+---------------+---------+
| 1  | Maria Martinez  | Maria Martinez  | Boston        | USA     |
| 2  | Donna Williams  | James Williams  | Seattle       | USA     |
| 3  | James Smith     | Andrew Smith    | Los Angeles   | USA     |
| 4  | Jennifer Garcia | Charles Garcia  | Las Vegas     | USA     |
| 5  | Google          | Jane Jackson    | Mountain View | USA     |
| 6  | Microsoft       | Samantha Garcia | Seattle       | USA     |
| 7  | Electronic Arts | Joe Smith       | Redwood City  | USA     |
| 8  | Tyson Foods     | Derek Anderson  | Springdale    | USA     |
+----+-----------------+-----------------+---------------+---------+

The following SQL statement copies only suppliers from "Seattle" city:

INSERT INTO client (name, contact_name, city, country)
SELECT supplier_name, contact_name, city, country 
FROM supplier
WHERE city= 'Seattle';

After the execution, the output from the "Client" table will be as follows:

+----+-----------------+-----------------+-------------+---------+
| id | name            | contact_name    | city        | country |
+----+-----------------+-----------------+-------------+---------+
| 1  | Maria Martinez  | Maria Martinez  | Boston      | USA     |
| 2  | Donna Williams  | James Williams  | Seattle     | USA     |
| 3  | James Smith     | Andrew Smith    | Los Angeles | USA     |
| 4  | Jennifer Garcia | Charles Garcia  | Las Vegas   | USA     |
| 5  | Microsoft       | Samantha Garcia | Seattle     | USA     |
+----+-----------------+-----------------+-------------+---------+