CODEWITHSUNDEEP
sql-servert-sqltemp-tables
Richard
Richard

Reputation: 53

How to display a temp table where values are inserted dynamically

Basically I have this TSQL which looks up a condition in all tables in a database and trying to insert the records into this temp table. I'm having difficulty to display this temp table #TEMP1 at the end of execution.

OPEN MyCursor;
FETCH NEXT FROM MyCursor INTO @Table
   WHILE @@FETCH_STATUS = 0 BEGIN 
        set @SQL = N'IF EXISTS(select 1 from Prod.' + @Table + ' where usedDate IS NULL)' + 
 'select ''' + @Table + ''' as TableName,* INTO #TEMP1 from Prod.' + @Table + ' WHERE usedDate IS NULL '

 EXECUTE sp_executesql @SQL
 FETCH NEXT FROM MyCursor INTO @Table
END;
CLOSE MyCursor;
DEALLOCATE MyCursor;

Upvotes: 0

Views: 159

Answers (1)

Srinika Pinnaduwage
Srinika Pinnaduwage

Reputation: 1042

Instead of #TEMP1 in

'select ''' + @Table + ''' as TableName,* INTO #TEMP1 from Prod.' + @Table + ' WHERE usedDate IS NULL '

You can use global temp ##TEMP1 table as 

'select ''' + @Table + ''' as TableName,* INTO ##TEMP1 from Prod.' + @Table + ' WHERE usedDate IS NULL '

But note that, if cursor iterates, this will fail as you cannot insert into a table more than once.

To address that issue

concatenate the value of @Table to ##TEMP1

or before the next iteration, drop the table ##TEMP1

Upvotes: 1

Related Questions