Nested SQL Cursors

Question

In my products table I have 14 products, and for every product I need a row for every state in the US. I'm trying to accomplish this by the following:

begin tran
declare @state varchar(2),
        @productId int

declare stateCursor CURSOR FOR
select distinct [State]
from lookUpAreaFactor

declare productCursor CURSOR FOR
select distinct productid 
from Product


open stateCursor
open productCursor
FETCH NEXT from stateCursor into @state
fetch next from productCursor into @productId
while @@FETCH_STATUS = 0
BEGIN

        while @@FETCH_STATUS = 0
        BEGIN
            insert into ProductToState (ProductID,[State]) values (@productId,@state)
            fetch next from productCursor into @productId
        END
    fetch next from stateCursor into @state
END

close stateCursor
close productCursor
select * from producttostate 
rollback

It's bombing out after the first product - but it isn't even inserting a row for all 50 states. I could use another pair of eyes - What am I doing wrong here?

Answer 1

Why are you using cursors for something that is a set-based operation?

I think this query does what you want:

insert into ProductToState(ProductID, [State])
    select ProductId, State
    from (select distinct [State] from lookUpAreaFactor) s cross join
         (select distinct ProductId from Product) p;