CODEWITHSUNDEEP
sql-servercursor
user3266033
user3266033

Reputation: 157

sql cursor insert result into a table

I have created a cursor which iterates through all the databases and displays the 1 record per database.

I would like the records to be inserted into 1 table where I can view it. The query may change which is why I don't want to create the table structure for a specific query and insert it. I wanted to use the "select into" clause but that will fail on the second time the cursor runs

DECLARE @DB_Name varchar(100) 
DECLARE @Command nvarchar(200)
DECLARE database_cursor CURSOR FOR SELECT name FROM #DBNAME

OPEN database_cursor

FETCH NEXT FROM database_cursor INTO @DB_Name

WHILE @@FETCH_STATUS = 0 
BEGIN 
     SELECT @Command = 'use [' + @DB_Name + '] Select '''+ @DB_Name + ''' ,'+

 --Enter query below
 '* from authentication where username like ''%clair@indicater%'' and password = ''Rohan2410'''

 --  print @Command
 EXEC sp_executesql @Command


 FETCH NEXT FROM database_cursor INTO @DB_Name 
END

CLOSE database_cursor 
DEALLOCATE database_cursor

Upvotes: 0

Views: 28672

Answers (1)

tomislav_t
tomislav_t

Reputation: 527

You should better use INSERT INTO ... instead of SELECT INTO, something like this:

DECLARE @DB_Name varchar(100) 
DECLARE @Command nvarchar(200)
DECLARE database_cursor CURSOR FOR SELECT name FROM #DBNAME

OPEN database_cursor

FETCH NEXT FROM database_cursor INTO @DB_Name

WHILE @@FETCH_STATUS = 0 
BEGIN

     SELECT @Command = 'use [' + @DB_Name + '] 

     IF OBJECT_ID(''tempdb..##output'') IS NULL
         BEGIN
            SELECT NULL AS DB_Name,* 
            INTO ##output
            FROM authentication WHERE 1=0
         END

     INSERT INTO ##output
     Select '''+ @DB_Name + ''' ,'+

 --Enter query below
 '* from authentication where username like ''%clair@indicater%'' and password = ''Rohan2410'''

 --  print @Command
 EXEC sp_executesql @Command


 FETCH NEXT FROM database_cursor INTO @DB_Name 
END

CLOSE database_cursor 
DEALLOCATE database_cursor

SELECT * FROM ##output

DROP TABLE ##output

Basically, on the first cursor iteration we will create an empty temp table with the correct structure. Then we just insert into that temp table.

Upvotes: 1

Related Questions