Esat Erkec
Execution plan of a SELECT INTO Statement

SELECT INTO TEMP TABLE statement in SQL Server

June 21, 2021 by

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

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 statement for temporary tables

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 :

A result set of a SELECT INTO statement

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.

Give alias to the column names in SQL Server

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.

Apply filter to a SELECT statement

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.

Comparing INSERT INTO SELECT  and SELECT INTO statements

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.

Execution plan of a SELECT INTO  Statement

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.

SELECT INTO and parallel execution plan

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.

SELECT INTO and ORDER BY statement relation

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.

Esat Erkec
168 Views