Insert Cursor Results Into Temp Table

[bustercoder]

Hello, I have this query which returns me the results from multiple databases, but I'm not sure how to do an "INSERT INTO" to a temp table? Could someone please show me an example of this?

DECLARE @exec_context varchar(30)
declare @sql nvarchar(4000)
DECLARE @DBNAME nvarchar(50)
DECLARE companies_cursor CURSOR FOR
SELECT DBNAME
FROM DBINFO
WHERE DBNAME NOT IN ('master', 'tempdb', 'msdb', 'model')
ORDER BY DBNAME
OPEN companies_cursor
FETCH NEXT FROM companies_cursor INTO @DBNAME
WHILE @@FETCH_STATUS = 0
BEGIN
set @exec_context = @DBNAME + '.dbo.sp_executesql '
set @sql = N'select top 10 * from products'
exec @exec_context @sql
FETCH NEXT FROM companies_cursor INTO @DBNAME
END
CLOSE companies_cursor
DEALLOCATE companies_cursor

Thanks in advance,
Buster

 

[bborissov]

declare #temp table first with the same structure as PRODUCTS table, then:

CREATE TABLE #Test (field list here)
declare @sql nvarchar(4000)
DECLARE @DBNAME nvarchar(50)

DECLARE companies_cursor CURSOR FOR
 SELECT NAME
        FROM sysdatabases
        WHERE OBJECT_ID(Name+'.dbo.products') IS NOT NULL
        ORDER BY NAME
OPEN companies_cursor
FETCH NEXT FROM companies_cursor INTO @DBNAME
WHILE @@FETCH_STATUS = 0
    BEGIN
        set @sql = N'select top 10 * from '+@DBNAME+'.dbo.products'
        INSERT INTO #Test
        exec (@sql)
        FETCH NEXT FROM companies_cursor INTO @DBNAME
    END
CLOSE companies_cursor
DEALLOCATE companies_cursor
SELECT * from #Test
DROP TABLE #Test

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[bustercoder]

Thank you Borislav!!

 

[TysonLPrice]

A short cut to creating the field list can be used:

select top 1 * into #temp from PRODUCTS
delete from #temp

Then use it.

 

[bustercoder]

Thanks. Can you show me how I can insert into a temp table using the @exec_context from my original query? The examples here are great, but they are not making the call from within a different context.

 

[bustercoder]

Thanks. Can you show me how I can insert into a temp table using the @exec_context from my original query? The examples here are great, but they are not making the call from within a different context.