How to store large sets of data temporarily into an object

Problem: How to store large sets of data temporarily into an object.

Solution: Temporary Tables.

This is a great feature that allows to store and process intermediate results. These tables can be used to insert, update, delete and can be used in joins as well. There are two types of temporary tables.

1. Local Temporary Table - specific to the session they are executed

2. Global Temporary Table - multiple sessions can access the data.

Tables needed to prefixed with # for Local Table and ## for Global Table.

Temporary Tables can be indexed as physical tables for performance boost or for fast retrieval.

Example:

In this article I am going to show you how we can create and use temporary tables.

Method 1: To create a table explicitly

IF OBJECT_ID('tempdb..#Test') IS NOT NULL

    DROP TABLE #Test

CREATE TABLE #Test

(

    Col1                TINYINT,

    Col2                TINYINT,

    Col3                TINYINT,

                Col4                                                       TINYINT

)

INSERT INTO #Test(Col1,Col2,Col3,Col4)

select  1, 2, 3,4

UNION

select  2, 3,4 ,5

select * from #Test

Method 2: To copy the structure by using INTO keyword.

IF OBJECT_ID('tempdb..#Test1') IS NOT NULL

    DROP TABLE #Test1

SELECT * INTO #Test1 FROM (select  1 as col1, 2 as col2, 3 as col3,4  as col4

UNION

select  2, 3,4 ,5) A

select * from #Test1

Index Example:

CREATE NONCLUSTERED INDEX IDX_Test_Col1_col2

ON #Test(col1,Col2)

OUTPUT: