Execute the following Microsoft SQL Server T-SQL example script in Management Studio Query Editor to create the global temporary table ##Washington with SELECT INTO.

A global temporary table is visible to any session (connection on server) while in existence. It lasts until all users that are referencing the table disconnect.

A local temporary table, like #California below, is visible only the local session (connection) and child session created by dynamic SQL (sp_executeSQL). The temporary table is localized by an auto-generated suffix which is added to the table name. It is deleted after the user disconnects.

In some applications global temporary tables offer better performance than local or table variables.

-- Create global temporary table with select into - SQL select into create table

USE AdventureWorks;

SELECT c.LastName, c.FirstName, a.City, s.StateProvinceCode

INTO ##Washington

FROM Person.Contact c JOIN HumanResources.Employee e

      ON c.ContactID = e.ContactID

INNER JOIN HumanResources.EmployeeAddress ea

      ON e.EmployeeID = ea.EmployeeID

INNER JOIN Person.Address a

      ON ea.AddressID = a.AddressID

INNER JOIN Person.StateProvince s

      ON a.StateProvinceID = s.StateProvinceID

WHERE s.StateProvinceCode = 'WA';

GO

SELECT TOP (5) * FROM ##Washington

ORDER BY NEWID()

GO

/* Results

LastName    FirstName   City        StateProvinceCode

Ford        Jeffrey     Monroe      WA

Zwilling    Michael     Edmonds     WA

Tibbott     Diane       Kenmore     WA

Harrington  Mark        Issaquah    WA

Nay         Lorraine    Edmonds     WA

*/

-- Cleanup

DROP TABLE ##Washington

GO

-- Create local temporary table with select into - SQL select into create table

USE AdventureWorks;

SELECT c.LastName, c.FirstName, a.City, s.StateProvinceCode

INTO #California

FROM Person.Contact c JOIN HumanResources.Employee e

      ON c.ContactID = e.ContactID

INNER JOIN HumanResources.EmployeeAddress ea

      ON e.EmployeeID = ea.EmployeeID

INNER JOIN Person.Address a

      ON ea.AddressID = a.AddressID

INNER JOIN Person.StateProvince s

      ON a.StateProvinceID = s.StateProvinceID

WHERE s.StateProvinceCode = 'CA';

GO

SELECT TOP (5) * FROM #California

ORDER BY NEWID()

GO

/* Results

LastName    FirstName   City              StateProvinceCode

Raheem      Michael     San Francisco     CA

Ito         Shu         San Francisco     CA

*/

-- Cleanup

DROP TABLE #California

GO