SQL INSERT INTO SELECT Statement

In SQL, the INSERT INTO statement is used to add or insert records into the specified table. We use this statement to insert data directly into a table by specifying column names in a specific order. The SELECT statement is used to retrieve data from the table, and it can be used in conjunction with the INSERT INTO statement to insert data from one table into another.

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

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 proceeding to examples, Let's first create two tables in our database to serve as the source and destination for our INSERT INTO SELECT statement examples. These tables will contain sample data to illustrate how the statement works effectively.

Table 1: geeksforgeeks

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

Table 2: users

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

Next, we insert some sample data into our users table:

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);

Step 1: Displaying Data in users Table

SELECT * FROM users;

Output

Example of SQL INSERT INTO SELECT Statement

The SQL INSERT INTO SELECT statement allows us to copy data from one table into another directly. This can be useful when we want to quickly populate a new table with data that meets certain criteria from an existing table. Below, we provide examples to illustrate how this statement can be used effectively.

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

In this example, we are copying all columns from the "users" table to the "geeksforgeeks" table without applying any filters, allowing us to create an exact replica of the "users" table in "geeksforgeeks".

Query

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

SELECT * FROM geeksforgeeks;

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

Here, we'll demonstrate how to use the INSERT INTO SELECT statement with a WHERE clause to filter and insert specific records from one table into another based on certain conditions. This allows us to selectively copy data from the source table to the destination table based on defined criteria.

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

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

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

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.