SQL with Examples : SQL INSERT SELECT Statement

Hits: 10

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.

ID Name Age City Job Salary
1 Bob 28 New York Manager 60000
2 Eve 24 Chicago Developer 32000
3 Max 26 New York Developer 29000
4 Kim 25 Chicago Manager 55000

The second table contains basic information about interns.

ID Name Age City Job Salary
5 Joe 23 New York Developer 9000
6 Sam 27 Chicago Developer 10000

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:

ID Name Age City Job Salary
1 Bob 28 New York Manager 60000
2 Eve 24 Chicago Developer 32000
3 Max 26 New York Developer 29000
4 Kim 25 Chicago Manager 55000
5 Joe 23 New York Developer 9000
6 Sam 27 Chicago Developer 10000

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’.

Caution:

Although this syntax is simple, it is not at all safe and should generally be avoided; Since it is highly dependent on the order in which the columns are defined in both the source and target tables.

Even though the order of the two matches now, there is no guarantee that the columns will be in the same order when one of them is rebuilt next time.

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:

ID Name Age City Job Salary
1 Bob 28 New York Manager 60000
2 Eve 24 Chicago Developer 32000
3 Max 26 New York Developer 29000
4 Kim 25 Chicago Manager 55000
5 Joe 23 New York Developer 9000
6 Sam 27 Chicago Developer 10000

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:

ID Name Age City Job Salary
1 Bob 28 New York Manager 60000
2 Eve 24 Chicago Developer 32000
3 Max 26 New York Developer 29000
4 Kim 25 Chicago Manager 55000
5 Joe 23 New York Developer 9000
6 Sam 27 Chicago Developer 10000

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:

ID Name Age City Job Salary
1 Bob 28 New York Manager 60000
2 Eve 24 Chicago Developer 32000
3 Max 26 New York Developer 29000
4 Kim 25 Chicago Manager 55000
6 Sam 27 Chicago Developer 10000

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:

ID Name Age City Job Salary
1 Bob 28 New York Manager 60000
2 Eve 24 Chicago Developer 32000
3 Max 26 New York Developer 29000
4 Kim 25 Chicago Manager 55000
5 Joe 23 NULL Developer NULL
6 Sam 27 NULL Developer NULL

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

Waring!

If you plan to skip a column, then you need to make sure that one of the following conditions exists for that column:

  1. The column is defined to allow NULL values.
  2. A default value for the column is specified in the table definition.

If you omit a column that does not allow NULL values and does not have a default value, then the INSERT statement fails and an error is generated.

 

Python Example for Beginners

Two Machine Learning Fields

There are two sides to machine learning:

  • Practical Machine Learning:This is about querying databases, cleaning data, writing scripts to transform data and gluing algorithm and libraries together and writing custom code to squeeze reliable answers from data to satisfy difficult and ill defined questions. It’s the mess of reality.
  • Theoretical Machine Learning: This is about math and abstraction and idealized scenarios and limits and beauty and informing what is possible. It is a whole lot neater and cleaner and removed from the mess of reality.

Data Science Resources: Data Science Recipes and Applied Machine Learning Recipes

Introduction to Applied Machine Learning & Data Science for Beginners, Business Analysts, Students, Researchers and Freelancers with Python & R Codes @ Western Australian Center for Applied Machine Learning & Data Science (WACAMLDS) !!!

Latest end-to-end Learn by Coding Recipes in Project-Based Learning:

Applied Statistics with R for Beginners and Business Professionals

Data Science and Machine Learning Projects in Python: Tabular Data Analytics

Data Science and Machine Learning Projects in R: Tabular Data Analytics

Python Machine Learning & Data Science Recipes: Learn by Coding

R Machine Learning & Data Science Recipes: Learn by Coding

Comparing Different Machine Learning Algorithms in Python for Classification (FREE)

Disclaimer: The information and code presented within this recipe/tutorial is only for educational and coaching purposes for beginners and developers. Anyone can practice and apply the recipe/tutorial presented here, but the reader is taking full responsibility for his/her actions. The author (content curator) of this recipe (code / program) has made every effort to ensure the accuracy of the information was correct at time of publication. The author (content curator) does not assume and hereby disclaims any liability to any party for any loss, damage, or disruption caused by errors or omissions, whether such errors or omissions result from accident, negligence, or any other cause. The information presented here could also be found in public knowledge domains.