|
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
|
