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