SQL Server 2008: Help in optimizing a query using a cursor

I need to rewrite this so it does not cause an o开发者_StackOverflow社区verrun on my db. I need to rewrite it so it does not use a cursor.

Thank you.

---

UPDATED Here's some test data:

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)

Then you can try a recursive 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

Here there's no cartesian product.

---

You can easily substitute a while loop for a cursor and if you are blowing out your tempdb then do some intermediate transactions

---

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

---

May be use less number of times the insert happen

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