Is use of while loop in stored procedure causing System.OutOfMemoryException?

Question

I have been struggling with System.OutOfMemoryException. I have seen some solution but all are saying you need more RAM.I doubt if it is due to inefficiency of code.So let me share my problem.
I have 10 different tables with around 5k records in each, I need to select one column from each table and construct a new table.I am able to insert around 1.5k records but then execution stop with "System.OutOfMemoryException" . My while loop looks like

ALTER PROCEDURE Sp_sample
As 
    Select col1
    into
    #ControlTable
    from 
    tab1

while exists(select * from #ControlTable)
    begin

            (select count(*) from #ControlTable);
            select @var1 = (select top 1 col1 from #ControlTable);          
            select @var2 = (select top 1 col2 from table1 where col3=@var1);
            if exists (select a from tablenew where col1=@var1)
            begin               
                update tablenew set col2 = @var2 where col1 = @var1
            end
            else
            begin           
                insert into tablenew values (@var1,@var2)
            end
            delete from #ControlTable where col1 = @var1;   
    end
Begin

I have posted sample code to make question more generic. Any help or suggestion will be highly appreciated.

Answer 1

Please try below while loop and check performance:

ALTER PROCEDURE Sp_sample
As 
Select col1, ROW_NUMBER() OVER(Order By col1) AS RowNo
into
#ControlTable
from 
tab1

DECLARE @Index INT=1;
DECLARE @TotalRow INT=0;

SELECT @TotalRow=COUNT(col1) FROM #ControlTable

while @Index<=@TotalRow
begin            
        select @var1 =  var1 from #ControlTable where RowNo=@Index;          
        select @var2 = var2 from table1 where col1=@var1;

        if exists (select a from tablenew where col1=@var1)
        begin               
            update tablenew set col2 = @var2 where col1 = @var1
        end
        else
        begin           
            insert into tablenew values (@var1,@var2)
        end
        SET @Index = @Index+1;
end
Begin