SQL INSERT SELECT Statement

INSERT statement is commonly used to insert a row into a table using specified values. There is a second form of INSERT that inserts the result of a SELECT statement in a table, known as INSERT SELECT.

As its name suggests, it is made up of an INSERT statement and a SELECT statement. This statement is especially useful when you need to insert one or more rows from one table into another.

Inserting rows this way does not affect any existing rows in either the source table or the target table.

Sample Table

To help you better understand the examples, and enable you to follow along with the tutorial, we are going to use the following sample tables.

The following table is part of an ‘Employee Management System’ that contains basic information about full-time employees.

The second table contains basic information about interns.

Insert All Rows From One Table Into Another

To insert all rows from one table into another table using the INSERT SELECT you must, at a minimum, specify two pieces of information – the source table name and the target table name. Just make sure that the columns and their data types in source and target tables match, although there’s no need for column names to match.

Here’s the basic INSERT SELECT syntax.

INSERT INTO target_table
SELECT *
FROM source_table;

Suppose you want to merge a list of interns from ‘Interns’ table into your ‘Employees’ table, you can do the following:

INSERT INTO Employees
SELECT *
FROM Interns;

The contents of the ‘Employees’ table after the insertion are:

This example imports all the data from ‘Interns’ into ‘Employees’. Instead of listing the VALUES to be inserted, the SELECT statement retrieves them from ‘Interns’.

The safer and recommended way to use the INSERT SELECT statement is to specify the column names explicitly. Just make sure that each column in the SELECT statement corresponds to the column listed in the INSERT statement.

INSERT INTO target_table(column1,column2,...)
SELECT column1,column2,...
FROM source_table;

The following example works exactly the same as the previous INSERT SELECT statement, but this time the column names are explicitly specified.

INSERT INTO Employees (ID, Name, Age, City, Job, Salary)
SELECT ID, Name, Age, City, Job, Salary
FROM Interns;

The contents of the ‘Employees’ table after the insertion are:

This example specifies the same column names in both the INSERT and SELECT statements, for the sake of clarity. However, there is no need to match column names. In fact, the DBMS takes into account the position of the columns in SELECT regardless of their names. So, the first column in SELECT is used to populate the first column in the INSERT statement, and so on.

The main advantage of this syntax is that, with the column names being explicitly specified, you can place them in any order. The following INSERT statement is the same as before, but specifies a column list in a different order.

INSERT INTO Employees (City, Name, Job, Age, Salary, ID)
SELECT City, Name, Job, Age, Salary, ID
FROM Interns;

The contents of the ‘Employees’ table after the insertion are:

Insert Selected Rows

The SELECT statement embedded in the INSERT statement is no different from the SELECT statement you use to retrieve data, so it can include a WHERE clause. And this WHERE clause filters the data to be copied.

INSERT INTO target_table(column1,column2,...)
SELECT column1,column2,...
FROM source_table
WHERE condition;

The following query inserts a single row from the ‘Interns’ table into the ‘Employees’ table.

INSERT INTO Employees (ID, Name, Age, City, Job, Salary)
SELECT ID, Name, Age, City, Job, Salary
FROM Interns
WHERE ID = 6;

The contents of the ‘Employees’ table after the insertion are:

Inserting Partial Rows

Using the above syntax, you can copy across only some of the columns. The DBMS will insert a NULL or a default value into columns that are not copied.

To demonstrate let’s import only the ‘ID’, ‘Name’, ‘Age’ and ‘Job’ columns from the ‘Interns’ table into the ‘Employees’ table.

INSERT INTO Employees (ID, Name, Age, Job)
SELECT ID, Name, Age, Job
FROM Interns;

The contents of the ‘Employees’ table after the insertion are:

As you can see in the example above, the ‘City’ and ‘Salary’ columns are inserted with NULLs.