Use Temp Table to merge query results from different DBs

Question

I need to extract data from different DBs into a single table. These DBs are all in the same Server and Instance and they have the same structure. One of the columns will be DB Name, the others come from the same table.

I could write a query that extracts these data with a table for each database, but I would like to merge all results into a single table.

I tried to use a temp table to save the single results but the result is an empty table. It seems that the table #tmpTable is emptied after each query. I post here my attempt:

CREATE TABLE [dbo].#tmpTable ([DbName] VARCHAR(MAX), [Example] VARCHAR(MAX))

EXECUTE sp_MSForEachDB 
    'USE ?;     
    DECLARE @ExampleQuery AS NVARCHAR(MAX) = 
             ''SELECT DB_NAME() AS [DbName], [Example] 
               INTO   #tmpTable 
               FROM   [tConfig]''
    EXEC sp_executesql @ExampleQuery;'

SELECT * FROM #tmpTable

DROP TABLE #tmpTable

The actual query is more complex and it's using PIVOT and other commands, but I think this example is enough if someone knows how to get the wanted result.

Answer 1

CREATE TABLE [dbo].#tmpTable ([DbName] VARCHAR(MAX))

EXECUTE sp_MSForEachDB 
    'USE ?;     
    DECLARE @ExampleQuery AS NVARCHAR(MAX) = 
             ''INSERT INTO #tmpTable SELECT DB_NAME() AS [DbName] ''
    EXEC sp_executesql @ExampleQuery;'

SELECT * FROM #tmpTable
DROP TABLE #tmpTable