Ever wanted to populate a table with data from another table? That’s where the INSERT statement with a subquery comes in handy. Using this method, we can insert the full contents of another table, or we can insert just a select number of rows or columns based on certain criteria.
Below are four examples of using the INSERT statement with a subquery, with each one slightly more complex than the previous.
Sample Database
First, let’s set up our sample data:
-- Create our tables
CREATE TABLE departments (
dept_id INT PRIMARY KEY,
dept_name VARCHAR(50)
);
CREATE TABLE employees (
emp_id INT PRIMARY KEY,
emp_name VARCHAR(50),
dept_id INT,
salary DECIMAL(10, 2)
);
CREATE TABLE projects (
project_id INT PRIMARY KEY,
project_name VARCHAR(50),
lead_emp_id INT
);
-- Populate departments
INSERT INTO departments (dept_id, dept_name) VALUES
(1, 'Wizardry'),
(2, 'Potions'),
(3, 'Divination');
-- Populate employees
INSERT INTO employees (emp_id, emp_name, dept_id, salary) VALUES
(1, 'Zaphod Beeblebrox', 1, 75000),
(2, 'Ford Prefect', 2, 65000),
(3, 'Trillian Astra', 1, 70000),
(4, 'Arthur Dent', 3, 60000),
(5, 'Slartibartfast', 2, 68000);Now that we’ve got our tables set up, let’s run some INSERT statements with subqueries.
Example 1: Basic INSERT with Subquery
Let’s start simple. We want to add all employees from the Wizardry department to our projects table as project leads:
INSERT INTO projects (project_id, project_name, lead_emp_id)
SELECT emp_id, CONCAT(emp_name, '''s Magical Venture'), emp_id
FROM employees
WHERE dept_id = 1;
-- Let's see what we've got
SELECT * FROM projects;Result:
project_id project_name lead_emp_id
---------- ----------------------------------- -----------
1 Zaphod Beeblebrox's Magical Venture 1
3 Trillian Astra's Magical Venture 3
In this example, we’re using the employee ID as the project ID, creating a project name based on the employee’s name, and setting the employee as the project lead.
Two employees match the subquery criteria, and so two rows are inserted into the projects table.
Example 2: Omitting the Column List
It’s possible to omit the column list, as long as the values we provide match the table structure exactly.
Here’s an example of what I mean:
INSERT INTO projects
SELECT emp_id + 100,
CONCAT('Secret Project ', CHAR(ASCII('A') + dept_id - 1)),
emp_id
FROM employees
WHERE salary > 65000;
-- Check out our new secret projects
SELECT * FROM projects;Result:
project_id project_name lead_emp_id
---------- ----------------------------------- -----------
1 Zaphod Beeblebrox's Magical Venture 1
3 Trillian Astra's Magical Venture 3
101 Secret Project A 1
103 Secret Project A 3
105 Secret Project B 5
Here, we’re creating secret projects for high-paid employees. We’re using a bit of math and string manipulation to generate project names on the fly.
Example 3: INSERT with Subquery and Join
Next, we’ll create projects based on department information and the highest-paid employee in each department. To do this, we’ll use a query that includes a join as well as a subquery:
INSERT INTO projects (project_id, project_name, lead_emp_id)
SELECT
d.dept_id + 200,
CONCAT(d.dept_name, ' Expansion'),
e.emp_id
FROM departments d
JOIN employees e ON d.dept_id = e.dept_id
WHERE e.salary = (
SELECT MAX(salary)
FROM employees e2
WHERE e2.dept_id = d.dept_id
);
-- Let's see our department expansion projects
SELECT * FROM projects;Result:
project_id project_name lead_emp_id
---------- ----------------------------------- -----------
1 Zaphod Beeblebrox's Magical Venture 1
3 Trillian Astra's Magical Venture 3
101 Secret Project A 1
103 Secret Project A 3
105 Secret Project B 5
201 Wizardry Expansion 1
202 Potions Expansion 5
203 Divination Expansion 4
We got the highest salary by using the MAX() function, which is a commonly used SQL aggregate function that returns the maximum value in the expression.
Example 4: INSERT with Subquery and Derived Table
It’s possible to include subqueries in the FROM clause. When we do this, the subquery creates a temporary table that can be queried like any other table. These queries are sometimes called derived tables or table expressions because the outer query uses the results of the subquery as a data source.
With that in mind, let’s create projects for employees who earn more than their department’s average salary:
INSERT INTO projects (project_id, project_name, lead_emp_id)
SELECT
e.emp_id + 300,
CONCAT('Overachiever - ', d.dept_name),
e.emp_id
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id
JOIN (
SELECT dept_id, AVG(salary) as avg_salary
FROM employees
GROUP BY dept_id
) avg_salaries ON e.dept_id = avg_salaries.dept_id
WHERE e.salary > avg_salaries.avg_salary;
-- Let's see who our overachievers are
SELECT * FROM projects;Result:
project_id project_name lead_emp_id
---------- ----------------------------------- -----------
1 Zaphod Beeblebrox's Magical Venture 1
3 Trillian Astra's Magical Venture 3
101 Secret Project A 1
103 Secret Project A 3
105 Secret Project B 5
201 Wizardry Expansion 1
202 Potions Expansion 5
203 Divination Expansion 4
301 Overachiever - Wizardry 1
305 Overachiever - Potions 5
This query uses a derived table to calculate the average salary for each department (using the SQL AVG() function), then compares each employee’s salary to their department’s average.
More Subqueries
See SQL INSERT INTO… SELECT Examples for more examples of inserting data based on a SELECT statement. You may find them slightly easier to follow than the above examples.
And see Understanding the SQL Subquery for examples of the various places we can put a subquery inside a query.