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 applications to large, enterprise-level databases. It provides features like parallel processing, indexing, and query optimization to ensure high performance.
In this article, We will understand the SQL Server SELECT INTO Statement along with the syntax and detailed implementation of various examples along with their output and so on.
SELECT INTO Statement in SQL Server
SELECT INTO is used to copy the result set into the new table. SELECT INTO consists of two operations first to create a table with columns that are to be stored and then the data is copied into that table.
There are different cases where we want to store the intermediate result, there we can use this. SELECT INTO is also used to create the backup of a table.
Syntax of SELECT INTO:
SELECT <select_list>
INTO new_table
FROM old_table
[WHERE condition];
Explanation: "Select_list" will contain the columns from which the data has to be copied. We can declare all the columns using (*) or we could give the subset of it. Based on these columns the schema of new_table is finalised. Where the condition is optional in this syntax.
Example of SELECT INTO Statement
To understand the SQL Server SELECT INTO Statement in good manner. We will need a table on which we will perform various queries. So we will create a Employee table and also insert some records into it.
Query to Create a table:
CREATE TABLE Employee
(
EmployeeID INT PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50),
Department VARCHAR(50),
Salary DECIMAL(10, 2)
);
Query to Insert data into it:
INSERT INTO Employee (EmployeeID, FirstName, LastName, Department, Salary)
VALUES
(1, 'John', 'Doe', 'IT', 60000.00),
(2, 'Jane', 'Smith', 'HR', 55000.00),
(3, 'Bob', 'Johnson', 'IT', 65000.00),
(4, 'Alice', 'Williams', 'Finance', 70000.00),
(5, 'Charlie', 'Brown', 'HR', 60000.00),
(6, 'David', 'Miller', 'Finance', 75000.00),
(7, 'Eva', 'Davis', 'IT', 62000.00),
(8, 'Frank', 'Clark', 'Finance', 72000.00),
(9, 'Grace', 'Moore', 'HR', 58000.00),
(10, 'Harry', 'Young', 'IT', 63000.00),
(11, 'Isabel', 'Hall', 'HR', 59000.00),
(12, 'Jack', 'Baker', 'Finance', 71000.00),
(13, 'Olivia', 'Turner', 'IT', 60000.00),
(14, 'Paul', 'Moore', 'Finance', 73000.00),
(15, 'Quinn', 'Parker', 'HR', 60000.00),
(16, 'Ryan', 'Scott', 'IT', 64000.00),
(17, 'Samantha', 'Bryant', 'HR', 61000.00),
(18, 'Tyler', 'Ward', 'Finance', 70000.00),
(19, 'Ursula', 'Hill', 'IT', 61000.00),
(20, 'Victor', 'Gomez', 'HR', 59000.00),
(21, 'Wendy', 'Fisher', 'IT', 62000.00),
(22, 'Xavier', 'Jordan', 'Finance', 71000.00),
(23, 'Yvonne', 'Lopez', 'HR', 58000.00),
(24, 'Zachary', 'Evans', 'IT', 63000.00),
(25, 'Ava', 'Hernandez', 'Finance', 69000.00);
Employee Table looks like:
Employee TableExplanation: Now we will take the backup of this table into an EmpBackup using the syntax discussed above. In this query we will take the data from employee table to the EmpBackup with the same schema of employee table.
Example 1: Simple SELECT INTO Statement
Let's Create a backup of the existing employee data stored in the "employee" table by copying all columns and rows into a new table named "EmpBackup." The backup table should be appropriately structured to preserve the data integrity of the original table.
Query:
SELECT *
INTO EmpBackup
FROM employee
Output:
Structure of EmpBackup
Explanation: This will create a new table named EmpBackup and the data is the same as the Employee table. Now let's have a look at the type of column.
We can see that the type of each column is the same as the type we have defined in the employee table. But in this table Primary Key is not copied because the creation of a new table is based on a select list and thus new table doesn't have any idea about any Constraint.
Example 2: SELECT INTO Using WHERE Clause
Now let's look at Select into with WHERE clause, here we have taking the data from employee table which are of department "HR", and then that data will only going to be inserted into EmpBackupHR.
Query:
SELECT *
INTO EmpBackupHR
FROM employee
WHERE department='HR'
Output:
EmpBackupHR
Explanation: The above query creates a department-specific backup named "EmpBackupHR" by selecting and copying records from the "employee" table where the department is 'HR.
Example 3: SELECT INTO Using JOIN
We can also use select into with join. We just have to add the INTO statement before the FROM clause. Select Into is executed at the end of all operation and here also firstly the join is executed and on that select into will work.
Let's Create a SQL query using the SELECT INTO statement to construct the ProductCategory table. The query should join the categories and products tables based on the category_id and include all columns from the products table, along with an additional category_name column
Query:
SELECT p.*, c.category_name
INTO ProductCategory
FROM categories c
JOIN products p
ON c.category_id=p.category_id
Output:
ProductCategory
Explanation: Here, a new table would be created using the columns from both tables.
Example 4: SELECT with INSERT INTO
Now we understand that SELECT INTO will create a table, and then data is inserted into the table. But what if the table is created and then we have to copy the data? For that, we use the Select statement with INSERT INTO.
Let's say we have a table EmpCopy which looks like:
EmpCopy
Now we have to copy the data of the employee table into this one. So, as the table is already created and thus we cannot use SELECT INTO. So, we will insert the data using an insert statement.
In the below query we are using the EmpCopy table which was created above and the data from the employee table is copied into it. Remember that the schema is not copied.
Query:
INSERT INTO EmpCopy
SELECT *
FROM Employee
Output:
EmpCopy
Explanation: The above query uses the INSERT INTO statement to add records into the "EmpCopy" table. The SELECT * FROM Employee retrieves all columns and rows from the "Employee" table, and these records are then inserted into the "EmpCopy" table, effectively creating a duplicate of the original data.
Conclusion
After reading whole article now have understand the SELECT INTO used for different scenarios. One of them is to create a backup table that will contain all the data of the main table. We can store the intermediate result in the temporary table to take a look at it. One of the best things about SELECT INTO is that it will also copy the schema of the table and thus we don't have to take care of it. We can also create a table with the schema of another table and not copy the data using the where clause which returns the false.
Similar Reads
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 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 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
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
What is Nested Select Statement in SQL Server
SQL Server is a powerful relational database management system. Sql Server is very good with its robustness and scalability. SQL Server operates as a client-server structure, imparting centralized management and scalability for huge-scale applications and enterprise-stage solutions. It offers advanc
3 min read
SQL Server SELECT INTO @Variable
In the world of SQL Server, the SELECT INTO statement is a powerful syntax for retrieving data from one or more tables and inserting it into a new table. However, what if you want to store the result set of a SELECT query into a variable rather than a table? This is where the SELECT INTO @Variable s
6 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
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 DELETE Statement
The SQL DELETE statement is one of the most commonly used commands in SQL (Structured Query Language). It allows you to remove one or more rows from the table depending on the situation. Unlike the DROP statement, which removes the entire table, the DELETE statement removes data (rows) from the tabl
4 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