SELECT * INTO TEMP TABLE

SELECT * INTO temp table is a SQL statement that creates a temporary table and populates it with data from a specified source. This operation is commonly used to store intermediate results for further processing within a query or stored procedure.

In this article, we shall unveil the details of this operation, exploring its different use cases and nuances. So, let’s embark on a journey to thoroughly understand this query.

What is SELECT * INTO?

The SELECT * INTO statement allows you to create a new table (usually a temporary table) and insert data into it in a single operation. The new table inherits the column structure from the result set of the SELECT statement. This can be useful when you want to store the results of a complex query temporarily for further processing.

Basic Syntax

SELECT *
INTO #TempTable
FROM SourceTable
WHERE Condition;

This query creates a new temporary table named #TempTable, copying the structure and data from SourceTable where the specified condition is met.

Temporary Tables in SQL

Temporary tables are stored in the tempdb database and exist only for the duration of the session or until they are explicitly dropped. These tables are prefixed with a ‘#’ (for local temporary tables) or ‘##’ (for global temporary tables).

How Do You Declare a Temp Table Variable in SQL?

You cannot declare a temp table variable in SQL.

There are two primary types of temporary structures in SQL:

Local Temporary Tables: These are created using the CREATE TABLE #TableName syntax and exist only within the current session.

Table Variables: These are declared using the DECLARE @TableName TABLE syntax and are scoped to the batch or stored procedure where they are declared.

Table variables are essentially in-memory structures that behave like tables but have limitations compared to traditional tables. They are primarily used for temporary storage within a batch or stored procedure.

Example:

DECLARE @MyTableVariable TABLE (
    Column1 int,
    Column2 varchar(50)
);

Creating a Temporary Table with SELECT * INTO

Consider a scenario where you need to analyze sales data but want to filter and manipulate the data before running your analysis. Instead of running multiple queries on the original Sales table, you can create a temporary table to hold the filtered data.

SELECT *
INTO #FilteredSales
FROM Sales
WHERE SaleDate BETWEEN '2023-01-01' AND '2023-12-31';

In this case, a temporary table #FilteredSales is created, containing only the records from the Sales table that fall within the specified date range. This table can then be queried, updated, or joined with other tables as needed.

Selecting Specific Columns into a Temp Table

While SELECT * grabs all columns, you might want only specific columns for your temporary table. This can be more efficient in terms of memory usage and processing time.

SELECT SaleID, ProductID, SaleAmount
INTO #SalesSummary
FROM Sales
WHERE SaleDate >= '2023-01-01';

Here, the temporary table #SalesSummary includes only the SaleID, ProductID, and SaleAmount columns. This selective approach reduces unnecessary data load and focuses only on the relevant columns.

Handling Large Datasets

When dealing with large datasets, creating a temporary table with SELECT * INTO can be resource-intensive. SQL Server must allocate space in tempdb, which could lead to performance bottlenecks if not managed carefully. To mitigate this, consider indexing the temporary table after creation:

CREATE INDEX idx_ProductID
ON #SalesSummary (ProductID);

Adding an index can significantly speed up queries that involve the indexed column, especially in scenarios with large datasets.

Using Temporary Tables in Joins

Temporary tables are particularly useful when you need to join large tables but only require a subset of the data from one of them. By moving this subset into a temporary table, you can optimize the join operation.

SELECT P.ProductName, S.SaleAmount
FROM Products P
JOIN #SalesSummary S ON P.ProductID = S.ProductID;

In this query, the #SalesSummary table is joined with the Products table, allowing for efficient retrieval of product names and their corresponding sales amounts.

Cleanup: Dropping Temporary Tables

Though temporary tables are automatically dropped at the end of a session, it’s good practice to drop them explicitly when they are no longer needed:

DROP TABLE #SalesSummary;

This helps free up resources in tempdb sooner and ensures that your session does not inadvertently reuse a temporary table with the same name.

Frequently Asked Questions

Can we create a temp table in function in SQL Server?

We can create a temporary table containing only the relevant data for our analysis. This allows us to work with a specific portion of the information without affecting the original data. Once the analysis is finished, we can delete the temporary table. 

What is the difference between SELECT INTO and INSERT INTO?

SELECT INTO creates a new table and inserts the selected data into it. INSERT INTO, on the other hand, inserts data into an existing table. Use SELECT INTO when you need to create a new table with data in one step, and INSERT INTO when the table structure is already defined.

How do I create a temp table from SELECT?

To manually create a temporary SQL table, we can use the CREATE TABLE statement with either the TEMPORARY or TEMP keyword before the table name. However, the exact syntax for this can vary between different database systems. 

Conclusion

Using SELECT * INTO to create temporary tables in SQL can greatly enhance your ability to work with complex datasets and optimize your queries. By understanding the nuances of this method and how to effectively use temporary tables, you can improve both the performance and clarity of your SQL operations.