Sql-server – Store the result from a cursor with exec into a temp table without explicitly named columns

sql serversql server 2014

I need to execute a query in each db I have got on my server and the result needs to be stored into a temp table. Due to the query model, I can't explicitly set column names for temp table. I've tried the below query but get "Invalid object name '#tmp'" error (temp table is not available outside the cursor loop). When I change #tmp to global ##tmp I get error "There is already an object named '##tmp' in the database".

Any idea how to bypass it in a simple way without openrowset?

DECLARE
@db VARCHAR(50),
@query VARCHAR(MAX), 
@sql VARCHAR(MAX)

SET @query = 'SELECT Col1=1, Col2=2 INTO #tmp'

DECLARE db_cursor CURSOR FOR 
SELECT db=name 
FROM MASTER.dbo.sysdatabases 
WHERE name NOT IN ('master','model','msdb','tempdb')

OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @db

WHILE @@FETCH_STATUS = 0  
BEGIN  
    SET @sql =' USE '+@db+'; ' +@query+''
    FETCH NEXT FROM db_cursor INTO @db
EXEC (@sql)
END 

CLOSE db_cursor  
DEALLOCATE db_cursor

SELECT * FROM #tmp

Best Answer

I assume that the query is a constant query. In that case, use this slightly modified code, using the global temp table theme;

DECLARE
    @db VARCHAR(50),
    @query VARCHAR(MAX), 
    @query2 varchar(max),
    @sql VARCHAR(MAX),
    -- make a unique global temp table name for now. Assumes that you drop
    -- the table explicitly at the end of each run.
    @tmpTbl varchar(50) = '##tmp_' + CAST(@@SPID as varchar) 

SET @query = 'SELECT Col1=1, Col2=2 INTO ' + @tmpTbl;
SET @query2 = 'INSERT INTO ' + @tmpTbl + ';'

DECLARE db_cursor CURSOR FOR 
SELECT db=name 
FROM MASTER.dbo.sysdatabases 
WHERE name NOT IN ('master','model','msdb','tempdb')

OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @db

WHILE @@FETCH_STATUS = 0  
BEGIN  
    SET @sql =' USE '+@db+'; ' +@query+''
    FETCH NEXT FROM db_cursor INTO @db
    EXEC (@sql)
    SET @query = @Query2; -- This will happen each time, but only
        -- adds a tiny bit of overhead to the process.
END 

CLOSE db_cursor  
DEALLOCATE db_cursor

SET @SQL = 'SELECT * FROM ' + @tmpSQL
EXEC (@sql)
SET @SQL = 'DROP TABLE ' + @tmpSQL
EXEC (@SQL)
Related Question