Insert Into Select statement in MS SQL Server
Last Updated : 13 Sep, 2024
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 Into Select statement in MS SQL Server by understanding various examples on detail and so on.
SQL Server INSERT INTO SELECT
- The
INSERT INTO SELECT statement in SQL Server is a powerful feature that allows us to copy data from one or more tables into another table. - This is useful for transferring data between tables, creating backups or merging datasets.
- We can insert all rows specific rows based on conditions, or even a subset of rows using filters such as
TOP or percentage limits.
INSERT INTO target_table (column1, column2, ...)
SELECT column1, column2, ...
FROM source_table
WHERE condition;
Explanation:
target_table: The table into which the data will be inserted.source_table: The table from which data is selected.condition (optional): Filters to specify which rows to select.
Examples of SQL Server INSERT INTO SELECT
Example 1: Insert All Rows from Another Table
Suppose we need to archive employee data by inserting all employee records from the Employees table into the EmployeesArchive table using a single query.
INSERT INTO EmployeesArchive (EmployeeID, FirstName, LastName)
SELECT EmployeeID, FirstName, LastName
FROM Employees;
Source Table (Employees):
| EmployeeID | FirstName | LastName |
| 1 | John | Doe |
| 2 | Jane | Smith |
| 3 | Bob | Johnson |
Output (EmployeesArchive after insertion):
| EmployeeID | FirstName | LastName |
| 1 | John | Doe |
| 2 | Jane | Smith |
| 3 | Bob | Johnson |
Explanation: This query copies all rows from the Employees table to the EmployeesArchive table. Every row from Employees will be inserted into the target table.
Example 2: Insert Some Rows from Another Table
Supose we need to insert employee records from the Employees table into the EmployeesArchive table for employees hired before January 1, 2020.
INSERT INTO EmployeesArchive (EmployeeID, FirstName, LastName)
SELECT EmployeeID, FirstName, LastName
FROM Employees
WHERE HireDate < '2020-01-01';
Source Table (Employees):
| EmployeeID | FirstName | LastName | HireDate |
| 1 | John | Doe | 2019-12-15 |
| 2 | Jane | Smith | 2020-05-10 |
| 3 | Bob | Johnson | 2018-03-22 |
Output (EmployeesArchive after insertion):
| EmployeeID | FirstName | LastName |
| 1 | John | Doe |
| 3 | Bob | Johnson |
Explanation: This query inserts only the rows from the Employees table where the HireDate is before January 1, 2020, into the EmployeesArchive table.
Example 3: Insert the Top N Rows
Suppose we need to insert the top 5 earliest hired employees from the Employees table into the EmployeesArchive table to create a snapshot of the most senior employees based on their hire date.
INSERT INTO EmployeesArchive (EmployeeID, FirstName, LastName)
SELECT TOP 5 EmployeeID, FirstName, LastName
FROM Employees
ORDER BY HireDate ASC;
Source Table (Employees):
| EmployeeID | FirstName | LastName | HireDate |
| 1 | John | Doe | 2019-12-15 |
| 2 | Jane | Smith | 2020-05-10 |
| 3 | Bob | Johnson | 2018-03-22 |
Output (EmployeesArchive after insertion):
| EmployeeID | FirstName | LastName |
| 3 | Bob | Johnson |
| 1 | John | Doe |
Explanation: This query selects and inserts the top 5 rows (earliest hired employees) from the Employees table into the EmployeesArchive table, based on HireDate.
Example 4: Insert the Top Percent of Rows
Suppose we need to insert the top 10% of employees, based on their hire date, from the Employees table into the EmployeesArchive table to archive the most senior employees.
INSERT INTO EmployeesArchive (EmployeeID, FirstName, LastName)
SELECT TOP 10 PERCENT EmployeeID, FirstName, LastName
FROM Employees
ORDER BY HireDate ASC;
Source Table (Employees):
| EmployeeID | FirstName | LastName | HireDate |
| 1 | John | Doe | 2019-12-15 |
| 2 | Jane | Smith | 2020-05-10 |
| 3 | Bob | Johnson | 2018-03-22 |
Output (EmployeesArchive after insertion):
| EmployeeID | FirstName | LastName |
| 3 | Bob | Johnson |
Explanation: This query inserts the top 10% of employees from the Employees table into the EmployeesArchive, based on their HireDate.
Conclusion
The INSERT INTO SELECT statement is a flexible and efficient way to transfer data between tables in SQL Server. It supports a wide range of use cases, including full table copying, conditional insertion, and even limiting the number of rows inserted. This versatility makes it an essential tool in data migration and management tasks.
Similar Reads
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
Select Statement in MS SQL Server
The SELECT statement in SQL Server is a foundational SQL command used for querying and retrieving data from one or more tables within a database. This command allows users to specify which columns and rows to retrieve and apply filters to focus on specific data and perform various operations to mani
4 min read
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
6 min read
Select into and temporary tables in MS SQL Server
In SQL Server, the SELECT INTO TEMP TABLE statement is used to select data from one or more source tables and insert it into a temporary table. Temporary tables are extremely useful when dealing with intermediate results, or when working with subsets of data within a session without modifying or aff
4 min read
DELETE Statement in MS SQL Server
The DELETE statement in MS SQL Server deletes specified records from the table. SyntaxMS SQL Server DELETE statement syntax is: DELETE FROM table_name WHERE condition; Note: Always use the DELETE statement with WHERE clause. The WHERE clause specifies which record(s) need to be deleted. If you exclu
1 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
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
Insert Multiple Values Into Multiple Tables in SQL Server
To insert multiple values into multiple tables in SQL Server, use the OUTPUT clause. The SQL OUTPUT clause allows to insert multiple values into multiple tables in a single query. Output clause clause in SQL is used to capture data affected by Data Manipulation Language (DML) statements like INSERT,
2 min read
SQL SERVER | Conditional Statements
While loop: In SQL SERVER, while loop can be used in similar manner as any other programming language. A while loop will check the condition first and then execute the block of SQL Statements within it as long as the condition evaluates true. Syntax: WHILE condition BEGIN {...statements...} END; Par
2 min read
SQL Server MERGE Statement
SQL Server MERGE statement combines INSERT, UPDATE, and DELETE operations into a single transaction. MERGE in SQL ServerThe MERGE statement in SQL provides a convenient way to perform INSERT, UPDATE, and DELETE operations together, which helps handle the large running databases. But unlike INSERT, U
2 min read