sql-server - SQL Server - 使用游标避免 tempdb 存储问题

Question

我需要从查询中将大量行插入到表中，这使我面临超出tempdb. 我需要一个可以避免这个问题的解决方案，并且正在考虑使用游标作为潜在的解决方案。在游标的每次迭代中都会有一个插入查询。我的问题是，游标中的每个插入查询是否有可能以tempdb与单个插入相同的方式超出空间？tempdb每次插入之间是否会自动清除存储的内部对象？

注意：我会自己做这个测试，但我们的团队出于某种原因只能访问生产服务器，而我们无法访问允许我监控的 DBA 工具tempdb。我不想冒险超出生产空间。

原来的：

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

光标解决方案：

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

标签： sql-servertempdb