SQL Temp Table, Table Variable and Global Table Variable

There are a few options when creating a table as part of a SQL operation. 

Notes: 

• The highlighted symbol is the indicator of the type of table.
• Temp table and table variable declaration/creation syntax is different

1.) Temp Table - This temp table is only active during the execution of the query and does not persist

DECLARE @tmp TABLE

(

Id INT,

Description VARCHAR(32)

)

INSERT INTO @tmp (ID, Description)

VALUES (1, 'test temp table')

2.)  Table Variable - This table will need DROPped as it will persist

CREATE TABLE #tmp 

(

Id INT,

Description VARCHAR(32)

)

INSERT INTO #tmp (ID, Description)

VALUES (1, 'test table variable')

DROP TABLE #tmp

3.)  Global Table Variable - This table will be accessible across query windows.  This table will need DROPped as it will persist.

CREATE TABLE ##globalTmp 

(

Id INT,

Description VARCHAR(32)

)

INSERT INTO ##globalTmp (ID, Description)

VALUES (1, 'test global table variable')

DROP TABLE ##globalTmp

SQL Pivot Table

A dynamic SQL pivot table can rearrange SQL output.  This can be useful if trying to format for Excel.

//Create a table variable

CREATE TABLE #yt

(

  [Store] int, 

  [Week] int, 

  [xCount] int

);

//Insert some values into the table

INSERT INTO #yt([Store], [Week], [xCount])

VALUES

    (102, 1, 96),

    (101, 1, 138),

    (105, 1, 37),

    (109, 1, 59),

    (101, 2, 282),

    (102, 2, 212),

    (105, 2, 78),

    (109, 2, 97),

    (105, 3, 60),

    (102, 3, 123),

    (101, 3, 220),

    (109, 3, 87);

//Generate the format of the top column values for the pivot table.  Week in this example.

DECLARE @cols AS NVARCHAR(MAX)

select @cols = STUFF((SELECT ',' + QUOTENAME(Week) 

                    from #yt

                    group by Week

                    order by Week

            FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)')  ,1,1,'')

//Generate the SQL query with the dynamic column data

DECLARE @query  AS NVARCHAR(MAX)

SET @query = 'select * 

from 

(

  select store, week, xCount

  from #yt

) src

pivot

(

  sum(xcount)

  for week in (' + @cols + ')

) piv;'

//Execute the query

exec(@query)

//The results will display with the store and week as the row/column values

store	1	2	3
101	138	282	220
102	96	212	123
105	37	78	60
109	59	97	87