Table of Contents
MySQL can be used to INSERT INTO a table from another table by using the INSERT INTO SELECT statement. This statement allows you to specify the columns you want to insert into, as well as the source table from which the data will be retrieved. You can also use a WHERE clause to filter the data being inserted. This method saves time and effort, as it eliminates the need to manually enter data into the destination table. It also ensures data consistency, as the data is being directly transferred from one table to another.
You can use the following syntax in MySQL to insert rows from another table:
INSERT INTO athletes1 (athleteID, team, points)
SELECT athleteID, team_name, total_points
FROM `athletes2`;
This particular example inserts the rows from the table named athletes2 for the columns named athleteID, team_name and total_points into the table named athletes1.
The following example shows how to use this syntax in practice.
Example: Use INSERT INTO From Another Table in MySQL
Suppose we create the following table named athletes1 that contains information about various basketball players:
-- create table
CREATE TABLE athletes1 (
athleteID INT PRIMARY KEY,
team TEXT NOT NULL,
points INT NOT NULL
);
-- insert rows into table
INSERT INTO athletes1 VALUES (0001, 'Mavs', 22);
INSERT INTO athletes1 VALUES (0002, 'Warriors', 14);
INSERT INTO athletes1 VALUES (0003, 'Nuggets', 37);
-- view all rows in table
SELECT * FROM athletes1;
Output:
+-----------+----------+--------+
| athleteID | team | points |
+-----------+----------+--------+
| 1 | Mavs | 22 |
| 2 | Warriors | 14 |
| 3 | Nuggets | 37 |
+-----------+----------+--------+ Then suppose we create another table named athletes2 that contains information about more basketball players:
-- create table
CREATE TABLE athletes2 (
athleteID INT PRIMARY KEY,
team_name TEXT NOT NULL,
total_points INT NOT NULL
);
-- insert rows into table
INSERT INTO athletes2 VALUES (0004, 'Lakers', 19);
INSERT INTO athletes2 VALUES (0005, 'Celtics', 26);
INSERT INTO athletes2 VALUES (0006, 'Thunder', 40);
-- view all rows in table
SELECT * FROM athletes2;
Output:
+-----------+-----------+--------------+
| athleteID | team_name | total_points |
+-----------+-----------+--------------+
| 4 | Lakers | 19 |
| 5 | Celtics | 26 |
| 6 | Thunder | 40 |
+-----------+-----------+--------------+ Suppose that we would like to insert all of the rows from athletes2 into athletes1.
We can use the following syntax to do so:
-- insert rows from athletes2 into athletes1
INSERT INTO athletes1 (athleteID, team, points)
SELECT athleteID, team_name, total_points
FROM `athletes2`;
-- view all rows in athletes1 table
SELECT * FROM athletes1;
+-----------+----------+--------+
| athleteID | team | points |
+-----------+----------+--------+
| 1 | Mavs | 22 |
| 2 | Warriors | 14 |
| 3 | Nuggets | 37 |
| 4 | Lakers | 19 |
| 5 | Celtics | 26 |
| 6 | Thunder | 40 |
+-----------+----------+--------+
Notice that all rows from athletes2 have been inserted into athletes1.
For example, we can use the following syntax to only insert the rows from athletes2 where the value in the total_points column is greater than 20:
-- insert rows from athletes2 into athletes1
INSERT INTO athletes1 (athleteID, team, points)
SELECT athleteID, team_name, total_points
FROM `athletes2`
WHERE total_points > 20;
-- view all rows in athletes1 table
SELECT * FROM athletes1;
+-----------+----------+--------+
| athleteID | team | points |
+-----------+----------+--------+
| 1 | Mavs | 22 |
| 2 | Warriors | 14 |
| 3 | Nuggets | 37 |
| 5 | Celtics | 26 |
| 6 | Thunder | 40 |
+-----------+----------+--------+ Notice that only the rows from athletes2 with a value greater than 20 in the total_points column have been inserted into athletes1.
Additional Resources
The following tutorials explain how to perform other common tasks in MySQL: