SQL Server Global Temp Table Example

By: Rick Dobson

Overview

In this section we look at how to create global temp tables.  These are similiar to the local temp tables, but are globabl in scope and any connection on the instance of SQL Server can interact with this table.

Creating and Populating SQL Server Global Temp Tables

The following code demonstrates how to create and populate global temporary tables. 

If you contrast the following script with the preceding script for local temp tables, it is easy to spot that table names begin with two leading hash signs (##).  In the following script, the global table names are ##not_married and ##married. 

Other than the leading double leading hash sign, the process for creating and populating a global temp table is the same as for a local temp table.

-- drop global temp tables
BEGIN TRY
  DROP TABLE ##not_married
END TRY
BEGIN CATCH
  PRINT '##not_married not available to drop'
END CATCH

BEGIN TRY
  DROP TABLE ##married
END TRY
BEGIN CATCH
  PRINT '##married not available to drop'
END CATCH

-- not married employees in global temp
SELECT
  [BusinessEntityID] INTO ##not_married
FROM [AdventureWorks2014].[HumanResources].[Employee]
WHERE [MaritalStatus] != 'M'

-- married employees in global temp
SELECT
  [BusinessEntityID] INTO ##married
FROM [AdventureWorks2014].[HumanResources].[Employee]
WHERE [MaritalStatus] = 'M'

After running the preceding script, you generate two global tables.  One of these is for all married employees; this table has 146 rows.  The other global table is for all employees who are not married; this table has 144 rows.  The sum of row counts for these two global temp tables matches the sum of row counts for the three local temp tables created on the previous page of this tutorial.