datetime tune date into pad dynamic cursor money percent sp job isnumeric while over update
FREE TRIAL  SQL 2012 PROGRAMMING  SEARCH
SQL Server Scripts SQL 2005 SQL 2008 Articles
SQL JOBS NEWS FORMAT DEV JOBS
How to create a global temporay table?

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


Exam Prep 70-461
Exam 70-461