About Me

My photo
Colorado
Paul has 18 years experience with Microsoft SQL Server. He has worked in the roles of production DBA, database developer, database architect, applications developer, business intelligence and data warehouse developer, and instructor for students aspiring for MCDBA certification. He has performed numerous data migrations and supported large databases (3 Terabyte, 1+ billion rows) with high transactions. He is a member of PASS, blogs about lessons learned from a developer’s approach to SQL Server administration, and has been the president of the Boulder SQL Server Users’ Group from January 2009 to present.

Tuesday, February 21, 2012

Populate a temp table from a dynamic query

Today somone on SQLServerCentral.com posted a question – how to populate a temp table with a dynamic SQL query.  The response I posted, shown below, is to use a global temp table, characterized by the double hash mark (##MyGlobalTemp).

The table will persist as long as you have a connection (spid) open that is accessing it. This may or may not be a good idea depending on your environment, but it will definitely work.

Regular temp tables are tricky to identify in metadata, since SQL Server will likely append some text to the name, to help keep the name unique.  Global temp tables, however, are easier to identify, so it is easier to check for its existence beforehand and create a "safe" process.


DECLARE @s varchar(1000)

IF object_id('tempdb.dbo.##MyGlobalTemp') Is Not Null
      DROP TABLE ##MyGlobalTemp


SET @s =
'SELECT *
INTO ##MyGlobalTemp
FROM (select name from sys.databases) as T'

Exec (@s)
go

SELECT *
FROM ##MyGlobalTemp

No comments:

Post a Comment