By: Rick Dobson
Overview
In this section of the tutorial, we will look at how to reference local and global temp tables when using a different connection than the connection used to create the local and global temporary tables.
Referencing Local and Global SQL Server Temp Tables in Another Connection
In SSMS, open a new query window other than the one used to create the temporary tables.
The we will run the two SELECT statements from a different connection than the one in which they were created and we will see the outcome is different.
-- count of rows for three local temp tables
-- with all employees in AdventureWorks2014
SELECT COUNT(*) count_from_local_temp_tables
FROM
(SELECT * FROM #married_female
UNION
SELECT * FROM #married_male
UNION
SELECT * FROM #not_married
) married_female_married_male_not_married
GO
-- count of rows for two global temp tables
-- with all employees in AdventureWorks2014
SELECT COUNT(*) count_from_global_temp_tables
FROM
(SELECT * FROM ##not_married
UNION
SELECT * FROM ##married
) not_married_married
So, if the local and global temp tables were created in one tab and run in a different tab, then the outcome would be different. A GO statement should follow the first SELECT statement to allow the connection to run both SELECTs even after a failure in the first select statement. The GO statement facilitates re-starting after an error.
The first query counting the rows in the set of three local temp tables fails. This is because the select query referencing local temp tables runs from a different connection than the connection used to create the local temp tables. The following Messages tab points attention to this failure with Msg 208. The error message indicates that the #married_female local temp table is not available in the second connection. This is because the #married_female local temp table is only available in the connection for the one in which it was created. Of course, the other two local temp tables (#married_male and #not_married) are also missing from the second tab, but the first error aborts the SELECT statement in the batch.
The second SELECT statement operates in a different batch because of the GO statement following the first SELECT statement. Also, this second SELECT statement queries two global temp tables. Because the second SELECT statement operates on global temp tables, it succeeds although it is from a different connection than the one used to create the global temp tables. The following Results tab image displays the outcome from the second query. This query returns a total count of employees of 290, which is known to be the correct number of employees in the Employees table of the HumanResources schema in the AdventureWorks2014 database.
