SQL Server 2008：帮助使用游标优化查询

Question

我需要重写它，这样它就不会导致我的数据库溢出。我需要重写它，使其不使用游标。

谢谢。

Answer 1

更新这是一些测试数据：

CREATE TABLE #TableB(SKU INT, BeginYear INT, EndYear INT, OptionA VARCHAR(10), OptionB VARCHAR(10))

INSERT INTO #TableB(SKU, BeginYear, EndYear, OptionA, OptionB)
VALUES  (1, 1920, 1950, 'option1', 'option1'),
        (1, 1980, 2001, 'option1', 'option2'),
        (1, 1940, 1952, 'option1', 'option1'), --overlapping years
        (2, 2001, 2005, 'option1', 'option1')

CREATE TABLE #TableA(SKU INT, OptionA VARCHAR(10), OptionB VARCHAR(10), Years INT)

然后您可以尝试递归 CTE：

;WITH CTE AS
(
    SELECT DISTINCT SKU, BeginYear, EndYear, OptionA, OptionB
    FROM #TableB
    UNION ALL
    SELECT SKU, BeginYear+1, EndYear, OptionA, OptionB
    FROM CTE 
    WHERE BeginYear < EndYear
)  
INSERT INTO #TableA(SKU, OptionA, OptionB, Years)
SELECT DISTINCT SKU, OptionA, OptionB, BeginYear
FROM CTE 
OPTION(MAXRECURSION 0)

SELECT *
FROM #TableA
ORDER BY SKU, OptionA, OptionB, Years

这里没有笛卡尔积。

Answer 2

您可以轻松地用 while 循环替换游标，如果您要耗尽 tempdb，则执行一些中间事务

---

declare @counter int

declare @temptable as tatble (RecID int Identity(1,1),
       SKU Varchar(25),
       OptionA Varchar(50),
       OptionB Varchar(50),
       CurrentYear Int,
       EndYear Int)

insert into @temptable
   Select SKU,OptionA, OptionB, BeginYear,EndYear
   From TableB

set @counter = (select max(recid) from @temptable)

begin transaction

while  @counter <> 0
Begin

    (***whatever sql logic***)

    if (@counter % 5000 = 0) and @@error = 0
        commit Transaction

    begin Transaction

    set @counter = @counter -1
End

commit transaction

Answer 3

插入发生的次数可能较少

Declare @temp table (
    SKU Varchar(25),
    OptionA Varchar(50),
    OptionB Varchar(50),
    [Year] Int
)

While @@FETCH_STATUS = 0
Begin
    While @CurrentYear <= @EndYear
    Begin
        Insert Into @temp(SKU, OptionA, OptionB, Years)
            Values(@SKU, @OptionA, @OptionB, @CurrentYear)                          

        Set @CurrentYear = @CurrentYear + 1
    End

    Insert Into TableA
        Select * From @temp

    Delete From @temp

    Fetch Next From @Row Into @SKU, @CurrentYear, @EndYear, @OptionA, @OptionB
End