SQL INSERT INTO SELECT Statement
Last Updated : 11 Dec, 2024
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
Users TableExample 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
GeeksforGeeks TableExplanation:
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
| id | name | potd |
|---|
| 100 | Vishu | 256 |
| 101 | Neeraj | 250 |
| 102 | Aayush | 200 |
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
| id | name | potd |
|---|
| 100 | Vishu | 256 |
| 102 | Aayush | 200 |
| 103 | Sumit | 210 |
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
| id | name | potd |
|---|
| 100 | Vishu | 256 |
| 101 | Neeraj | 250 |
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.
Similar Reads
SQL SELECT INTO Statement
The SELECT INTO statement in SQL is a powerful and efficient command that allow users to create a new table and populate it with data from an existing table or query result in a single step. This feature is especially useful for creating backups, extracting specific subsets of data, or preparing new
5 min read
SQL INSERT INTO Statement
The SQL INSERT INTO statement is one of the most commonly used commands for adding new data into a table in a database. Whether you're working with customer data, products, or user details, mastering this command is crucial for efficient database management. Let’s break down how this command works,
6 min read
MySQL INSERT INTO SELECT Statement
MySQL is an open-source relational database management system that uses Structured Query Language (SQL) to manipulate databases. It stores data in a table format. It provides various statements to perform Create, Read, Update, and Delete operations on a database table. INSERT INTO SELECT statement i
5 min read
MySQL INSERT INTO Statement
In MySQL, the INSERT INTO statement is essential for adding new data rows to a table in a database. This is important for setting up initial data in tables and for adding new records as needed when working with the database. Understanding how to use the INSERT INTO statement is key for managing and
6 min read
SQL SELECT IN Statement
The IN operator in SQL is used to compare a column's value against a set of values. It returns TRUE if the column's value matches any of the values in the specified list, and FALSE if there is no match. In this article, we will learn how IN operator works and provide practical examples to help you b
4 min read
SQLite INSERT INTO SELECT
SQLite is a lightweight and server-less relational database management system. It requires very minimal configuration which has proven to be very helpful for developers to integrate it into any applications with ease. Due to its server-less architecture, we can use SQLite in various mobile applicati
4 min read
Insert Into Select statement in MS SQL Server
The INSERT INTO SELECT statement in SQL Server is a versatile feature that enables you to efficiently copy data from one or more tables into another table. This functionality is essential for tasks such as data transfer, backup creation, and data merging. In this article, We will learn to Insert Int
4 min read
PL/SQL INSERT Statement
The PL/SQL INSERT statement is vital for adding new records to a database table. By specifying the table's name and providing values for its columns, users can populate their database with essential information. This functionality enables efficient data entry and ensures the completeness of datasets
3 min read
SQL Server SELECT INTO Statement
SQL Server is a relational database management system. SQL Server offers robust security features to protect data integrity and confidentiality. It includes authentication, authorization, encryption, and various mechanisms to secure the database environment. It is designed to scale from small applic
6 min read
Insert Statement in MS SQL Server
The SQL Server INSERT statement is a fundamental command used to add new rows of data to a table. Whether we are inserting specific values, utilizing default values or copying data from another table. In this guide, we’ll explore various ways to use the Insert statement in MS SQL Server with the hel
4 min read