0

I need to insert a very large number of rows into a table from a query, which puts me at risk of exceeding space on tempdb. I am in need of a solution that can avoid this issue, and am considering the use of cursors as a potential solution. Within each iteration of the cursor would be an insert query. My question is, would each insert query in a cursor have the potential to exceed space in tempdb in the same way that a single insert would? Are the internal objects stored in tempdb automatically cleaned out between each insert?

Note: I would do this testing myself, but our team for some reason only has access to the production server, and we have no access to the DBA tools that would allow me to monitor tempdb. I do not want to risk exceeding space on production.

Original:

insert into dbo.Customer_Result 
select
    GroupID
    , EndDate
    , ActiveDate
    , count(c.CUSTOMER_k),0) CustomerCount
    , sum(Balance),0) TotalBalances
from dbo.Customer_Cohorts c
inner join dbo.Customer_Active_Flags a
on c.CUSTOMER_k = a.CUSTOMER_k
group by GroupID, EndDate, ActiveDate

Cursor solution:

declare @Cursor cursor
set @Cursor = cursor for select EndDate from dbo.EndDates
open @Cursor 
fetch next from @Cursor 
into @CohortDate

while @@fetch_status = 0
    begin
    insert into dbo.Customer_Result 
    select
        GroupID
        , EndDate
        , ActiveDate
        , count(c.CUSTOMER_k),0) CustomerCount
        , sum(Balance),0) TotalBalances
    from dbo.Customer_Cohorts c
    inner join dbo.Customer_Active_Flags a
    on c.CUSTOMER_k = a.CUSTOMER_k
    where c.EndDate = @CohortDate
    group by GroupID, EndDate, ActiveDate

    -- are the internal objects stored in tempdb from the above insert automatically 
    -- cleaned out after the above insert is complete?

    fetch next from @Cursor 
    into @CohortDate
end
user1185790
  • 623
  • 8
  • 24
  • 1
    tempdb space is reused. It's like a scratchpad to prepare your data prior to insert. With that said, how your query executes may affect that, but this appears to be a simple join. Run "Display Estimated Execution Plan" from the "Query" menu (Ctrl+L) and hold your mouse over the different elements and you'll see how many rows it thinks it'll find for you in each iteration. – Alan Samet May 13 '20 at 16:13
  • @AlanSamet - right, every insert (in this example) would consist of 161 million rows, equating to 8.7 GB. This single insert would not have any impact on tempdb. Based on what you're saying, I should not expect the multiple inserts from the cursor to accumulate in tempdb? As in, assuming every insert equates to 8.7 GB, I should not expect tempdb to exceed this amount? – user1185790 May 13 '20 at 16:37

0 Answers0