0

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
  • WIll each temporary table have the same number of columns? – HandyD Sep 13 '18 at 4:12
  • You say you can't explicitly set the temp table columns, but you do that in your dynamic query. Please can you explain why you can't use an explicitly named temp table and the INSERT...EXEC pattern? Thanks – Mister Magoo Sep 13 '18 at 8:11
  • @HandyD: Yes, each temp table will have the same number of columns. – Przemyslaw Wojda Sep 13 '18 at 10:18
  • @Mister Magoo: I'll have tens of such queries in my final script which may change in time so I want to eliminate setting columns explicitly as a potential place for an error. – Przemyslaw Wojda Sep 13 '18 at 10:21
  • @PrzemyslawWojda understood, but you have named columns in your question - so perhaps you need to update the question to better indicate what you are trying to do? Perhaps give a few more examples of the types of queries and indicate whether they all use the same temp table or perhaps groups of queries will have separate temp tables -> e.g. 5 queries that can share a temp table, 3 queries that need a different temp table, etc... – Mister Magoo Sep 13 '18 at 10:27
1

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)
| improve this answer | |
  • Thank you. I've run the script but it ends with an error "Incorrect syntax near ';'" and I can't yet localize it. – Przemyslaw Wojda Sep 13 '18 at 10:24
  • I forgot to specify the SELECT statement in Query2. Oops – Laughing Vergil Sep 13 '18 at 14:47
0

Done.

DECLARE
    @db VARCHAR(50),
    @prequery VARCHAR(MAX), 
    @query VARCHAR(MAX), 
    @sql VARCHAR(MAX),
    @tmpTbl varchar(50) = '##tmp_' + CAST(@@SPID as varchar) 

SET @prequery = 'SELECT Col1=1, Col2=2';
SET @query = @prequery+' 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='INSERT INTO '+@tmpTbl+' '+@prequery

END 

CLOSE db_cursor  
DEALLOCATE db_cursor


SET @SQL = 'SELECT * FROM ' + @tmpTbl
EXEC (@sql)
SET @SQL = 'DROP TABLE ' + @tmpTbl
EXEC (@SQL)
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.