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 affecting the original tables.

In this article, We will learn about SELECT INTO TEMP TABLE is a SQL Server in detail by understanding various examples and so on.

SELECT INTO TEMP TABLE is a SQL Server

• SELECT INTO TEMP TABLE is a SQL statement used to select data from one or more tables and insert it into a temporary table?
• Temporary tables are useful when we need to store intermediate results or work with subsets of data within a session without affecting the main tables in the database.

The general syntax of this statement is:

SELECT columns
INTO TEMPORARY TABLE temp_table_name
FROM source_table
WHERE condition;

Key Points:

• Temporary Tables: These are created in a database session and are automatically dropped at the end of the session.
• INTO Clause: The INTO TEMPORARY TABLE clause is used to create the temporary table and populate it with the selected data.
• No Pre-existing Table: The temporary table does not need to pre-exist, as this command both creates the table and inserts data into it.

Examples of SELECT INTO TEMP TABLE is a SQL Server

This table contains four columns: EmployeeID, FirstName, LastName, Department, and Salary, with the corresponding data that was inserted.

Example 1: Create a Temporary Table with IT Department Employees

We want to create a temporary table with the employees who belong to the IT department from the Employees table.

Query:

SELECT EmployeeID, FirstName, LastName, Salary
INTO #IT_Employees
FROM Employees
WHERE Department = 'IT';

-- View data from the temporary table
SELECT * FROM #IT_Employees;

Output:

Explanation:

This query selects employees from the IT department and inserts the data into a temporary table #IT_Employees. The table is created dynamically and holds the data only for the duration of the session.

Example 2: Create a Temporary Table with High-Salary Employees

We want to create a temporary table that holds data for employees with a salary greater than 55,000 from the Employees table.

Query:

SELECT EmployeeID, FirstName, LastName, Department, Salary
INTO #HighSalaryEmployees
FROM Employees
WHERE Salary > 55000;

-- View data from the temporary table
SELECT * FROM #HighSalaryEmployees;

Output:

Explanation:

This query filters employees who earn more than 55,000 and inserts the result into a temporary table #HighSalaryEmployees, which is session-specific

Example 3: Create a Temporary Table and Calculate Average Salary by Department

We want to calculate the average salary by department from the Employees table and store the result in a temporary table.

Query:

SELECT Department, AVG(Salary) AS AvgSalary
INTO #AvgSalaryByDept
FROM Employees
GROUP BY Department;

-- View data from the temporary table
SELECT * FROM #AvgSalaryByDept;

Output:

Explanation:

This query groups employees by department and calculates the average salary. The result is inserted into a temporary table #AvgSalaryByDept, which stores the department and average salary.

INSERT INTO SELECT vs SELECT INTO TEMP TABLE

Conclusion

The SELECT INTO TEMP TABLE statement in SQL Server is a powerful tool for efficiently handling temporary data without affecting the main database. It simplifies the process by dynamically creating and populating temporary tables in one step, making it ideal for intermediate data operations.