SELECT INTO TEMP TABLE statement in SQL Server

In this article, we will explore the SELECT INTO TEMP TABLE statement, its syntax and usage details and also will give some simple basic examples to reinforce the learnings.

Introduction

SELECT INTO statement is one of the easy ways to create a new table and then copy the source table data into this newly created table. In other words, the SELECT INTO statement performs a combo task:

Creates a clone table of the source table with exactly the same column names and data types
Reads data from the source table
Inserts data into the newly created table

We can use the SELECT INTO TEMP TABLE statement to perform the above tasks in one statement for the temporary tables. In this way, we can copy the source table data into the temporary tables in a quick manner.

SELECT INTO TEMP TABLE statement syntax

SELECT * | Column1,Column2...ColumnN

INTO #TempDestinationTable

FROM Source_Table

WHERE Condition

Arguments of the SELECT INTO TEMP TABLE

Column List: We can use the asterisk (*) to create a full temporary copy of the source table or can select the particular columns of the source table
Destination Table: This table refers to the temporary table name to which we will create and insert the data. We can specify the destination table as a local or global temporary table. For the local temporary table, we use a single hash (#) sign and for the global temporary table we use hash (##) sign
Source Table: The source is a table from which we want to read data
Where Clause: We can use a where clause to apply a filter to the source table data

In the following example, we will insert the Location table data into the #TempLocation table. In other words, we will create a temporary clone of the Location table.

SELECT * INTO #TempLocation FROM Production.Location

SELECT * FROM #TempLocation

As we can see, the SELECT INTO statement creates the #TempLocation table and then insert the Location table data into it.

When we want to insert particular columns of the Location table into a temporary table we can use the following query :

SELECT LocationID,Name,ModifiedDate INTO #TempLocationCol FROM Production.Location

SELECT * FROM #TempLocationCol

One point to notice here is the temporary table and source table column names are the same. In order to change the column names of the temporary table, we can give aliases to the source table columns in the select query.

SELECT LocationID AS [TempLocationID],

Name AS [TempLocationName] ,ModifiedDate AS [TempModifiedDate]

INTO #TempLocationCol FROM Production.Location

SELECT * FROM #TempLocationCol

At the same time, we can filter some rows of the Location and then insert the result set into a temporary table. The following query filters the rows in which the Name column starts with the “F” character and then inserts the resultsets into the temporary table.

SELECT LocationID,Name,ModifiedDate INTO #TempLocationCon FROM Production.Location

WHERE Name LIKE 'F%'

SELECT * FROM #TempLocationCon

INSERT INTO SELECT vs SELECT INTO TEMP TABLE

INSERT INTO SELECT statement reads data from one table and inserts it into an existing table. Such as, if we want to copy the Location table data into a temp table using the INSERT INTO SELECT statement, we have to specify the temporary table explicitly and then insert the data.

---Declare the temporary table---

CREATE TABLE #CopyLocation(

LocationID smallint NOT NULL,

Name nvarchar(50) NOT NULL,

CostRate smallmoney NOT NULL,

Availability decimal (8, 2) NOT NULL,

ModifiedDate datetime NOT NULL)

---Copy data into the temporary table---

INSERT INTO #CopyLocation

SELECT * FROM Production.Location

---Select data from the temporary table---

SELECT * FROM #CopyLocation

In fact, these two statements accomplish the same task in different ways. However, they have some differences in their usage scenarios.

INSERT INTO SELECT	SELECT INTO
Required to declare the destination temporary table explicitly. So, it allows the flexibility to change column data types and able to allows creates indexes.	Creates the destination temporary table automatically.
Due to the flexibility to define the column data types, allows transferring data from different tables.	It can create a backup copy of a table with easy syntax.

SELECT INTO TEMP TABLE performance

The SELECT INTO TEMP TABLE statement performs two main tasks in the context of the performance and these are:

Reading data from the source data
Inserting data into the temp table

Data reading operation performance depends on the select query performance so we need to evaluate the performance of the data reading process within this scope. However, the configuration of the tempdb database will have an impact on the performance of the insert statement. With SQL 2014, SELECT … INTO statements have been running parallel so they show better performance. Now, let’s analyze the following query execution plan.

SELECT SalesOrderID,CarrierTrackingNumber,ModifiedDate

INTO #TempsSalesDetail FROM Sales.SalesOrderDetail

ORDER BY SalesOrderID

1- The Clustered Index Scan operator reads all data from the primary key of the SalesOrderDetail table and passes all data to the table insert operator.

2- The Table Insert operator adds new data into the temporary table and performs this operation in a parallel manner. This situation can be shown in the Actual Number of Rows attribute. Thread 0 does not show any values because it is the coordinator thread.

The Gather Stream operator merges several parallel operations into a single operation. In this query execution plan, we have used the ORDER BY clause but we can not see any sort of operator in the execution plan. At the same time, the Clustered Index Scan operator does not return in a sorted manner. The reason for this point is that there is no guarantee for the order of insertion of the rows into the table.

Conclusion

In this article, we have learned the syntax and usage details of the SELECT INTO TEMP TABLE statement. This statement is very practical to insert table data or query data into the temporary tables.

Author
Recent Posts

Esat Erkec

Esat Erkec is a SQL Server professional who began his career 8+ years ago as a Software Developer. He is a SQL Server Microsoft Certified Solutions Expert.

Most of his career has been focused on SQL Server Database Administration and Development. His current interests are in database administration and Business Intelligence. You can find him on LinkedIn.

View all posts by Esat Erkec