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 “w3wiki”. We will copy all the values of fields(id, name, potd) from “users” table.

Query

--Data insertion query

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

--For displaying data

SELECT * FROM w3wiki;

Output:

Explanation:

We can observe that all the data from the fields id, name, potd are copied from users table and inserted into our table w3wiki. As we have specified no conditions, therefore all the data from users table gets copied to the w3wiki 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 w3wiki table where the potd score is greater or equal to 210.

Query:

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

Output:

Explanation:

In the above displayed image we can observe that in this w3wiki table, no values in potd column are less than 210. Thus all the values from users table gets copied to our w3wiki 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 w3wiki table where the courses taken is greater or equal to 10 and rank should be less than or equal to 18.

Query:

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

Output:

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 w3wiki table. We can clarify this by matching the values of w3wiki 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 w3wiki(id,name,potd)
SELECT id, name, potd
FROM users WHERE potd > 200 AND ID NOT IN (103,104);

Output:

Explanation:

In the above image, we can observe that all those values from users table get inserted into w3wiki 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.

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.