SQL INSERT INTO SELECT Statement

In SQL, the INSERT INTO statement is used to add or insert records to the specified table. We can use this statement to add data directly to the table. We use the VALUES keyword along with the INSERT INTO statement. VALUES keyword is accompanied by column names in a specific order in which we want to insert values in them. SELECT statement is used to retrieve data from the table. We can use a SELECT statement along with a where clause too in order to fetch some specific data from the table.

In this article, we are going to learn how to use the INSERT INTO statement along with the SELECT statement. We will be going through its various examples along with their respective explanations. We will also see how it can be used in real-world situations.

SQL INSERT INTO SELECT Statement

In SQL, the INSERT INTO SELECT Statement is used to insert data into the table directly. The data which is inserted is the result we get from the SELECT statement. This statement is generally used when we want to copy some data from one table to another. There is a thing to remember, the data returned by the SELECT statement must be compatible with other table columns on which we try to insert them. Otherwise, it will throw us an error.

Syntax:

INSERT INTO table_01 (column_01, column_02,…………………)

SELECT (column_01, column_02,…………………)

FROM table_02;

Examples of INSERT INTO SELECT Statement

Before moving to some examples, we need to create at-least two table in our database first.

Table 1: geeksforgeeks

Let’s create a table in our database named “geeksforgeeks“.

Query

CREATE TABLE geeksforgeeks(
  id int PRIMARY KEY,
  name varchar(100),
  potd int
 );

Table 2: users

Let’s create another table in our database named “users”.

Query

CREATE TABLE users(
  id int PRIMARY KEY,
  name varchar(100),
  courses int,
  rank int,
  potd int
 );

Let’s insert some values in our table “users” and display them.

Query:

--Data Insertion
INSERT INTO users(id,name,courses,rank,potd)
VALUES(100,'Vishu',10,15,256);
INSERT INTO users(id,name,courses,rank,potd)
VALUES(101,'Neeraj',5,16,250);
INSERT INTO users(id,name,courses,rank,potd)
VALUES(102,'Aayush',20,17,200);
INSERT INTO users(id,name,courses,rank,potd)
VALUES(103,'Sumit',15,18,210);
INSERT INTO users(id,name,courses,rank,potd)
VALUES(104,'Harsh',25,19,150);

--Displaying Data 
SELECT * FROM users;

Output:

Table – users

Example of SQL INSERT INTO SELECT Statement

Example 1: INSERT INTO SELECT Statement Without Using WHERE Clause.

Let’s insert some values in our table “geeksforgeeks”. We will copy all the values of fields(id, name, potd) from “users” table.

Query

--Data insertion query

INSERT INTO geeksforgeeks(id,name,potd)
SELECT id, name, potd
FROM users;

--For displaying data

SELECT * FROM geeksforgeeks;

Output:

Table geeksforgeeks – without where statement

Explanation:

We can observe that all the data from the fields id, name, potd are copied from users table and inserted into our table geeksforgeeks. As we have specified no conditions, therefore all the data from users table gets copied to the geeksforgeeks table.

Example 2: INSERT INTO SELECT Statement With Using WHERE Clause

Under this example, we will explore various conditions under which we can implement INSERT INTO SELECT statement along with the WHERE clause.

Case 1: WHERE Clause in potd Column

In this case, we will insert all those values from the users table to our geeksforgeeks table where the potd score is greater or equal to 210.

Query:

INSERT INTO geeksforgeeks(id,name,potd)
SELECT id, name, potd
FROM users WHERE potd>=210;

Output:

where clause in potd column

Explanation:

In the above displayed image we can observe that in this geeksforgeeks table, no values in potd column are less than 210. Thus all the values from users table gets copied to our geeksforgeeks table where potd score is greater than or equal to 210.

Case 2: WHERE Clause in Courses and Rank Columns

In this case, we will insert all those values from users table to our geeksforgeeks table where the courses taken is greater or equal to 10 and rank should be less than or equal to 18.

Query:

INSERT INTO geeksforgeeks(id,name,potd)
SELECT id, name, potd
FROM users WHERE courses>=10 AND rank<=18;

Output:

Table geeksforgeeks – Where in courses and rank

Explanation:

From the above output, we can observe that all those values from users table where courses acquired are grater than or equal to 10 and where rank obtain is less than or equal to 18 are inserted into our geeksforgeeks table. We can clarify this by matching the values of geeksforgeeks table with the values of users table.

Case 3: WHERE Clause With NOT IN Statement

In this case, we will use WHERE Clause along with NOT IN statement. We will insert all those data from users table where potd score is greater than 200 and ID should not be equal to ‘103’, or ‘104’.

Query:

INSERT INTO geeksforgeeks(id,name,potd)
SELECT id, name, potd
FROM users WHERE potd > 200 AND ID NOT IN (103,104);

Output:

Table geeksforgeeks – WHERE Clause with NOT IN

Explanation:

In the above image, we can observe that all those values from users table get inserted into geeksforgeeks table which satisfies both the conditions i.e. potd score should be greater than 200 and ID should be equal to either 103 or 104.

Conclusion

In SQL, INSERT INTO SELECT Statement is generally used to copy data from one table to some other table. We can also filter out the first table’s data before inserting it into another table with the help of WHERE Clause. Thing to keep in mind while copying data from one table to another, is that data of the first table should be compatible with the data types of another table. Otherwise, this will throw us an error. In this article, we have covered all the concepts will clear and concise examples along with their respective explanations.