MS SQL Loop within a loop

I have two tables - one is called #final and has a lot of customer data (composite of all information). Dozen columns or so - first_name, middle_name, last_name, address, attendees etc. I've also got a second table

create table #diff_temp
(
customer_no int,
num_reception int,
num_guests int,
diff int
)

the way the second table is populated with a customer ID, total reception and guest tickets and the difference between diff = reception-guests

What I need to do is run a loop and insert name, address information into #final the amount of times that equals diff per record.

For example.

#diff_Temp has data that looks like this.

customer_no num_reception   num_guests  diff
1           5               1           4
2           12              10          2
3           3               1           2
4           32              20          12
5           12              10          2
6           8               6           2

What i need to happen is that there is a loop for customer 1 the loop runs 4 times and data is entered 4 times into #final. For customer 2 the loop will run 2 times, for customer 4 it will run 12 times, and so on.

For each customer the loop runs amount times of the value in the diff column. The loop then inserts data into #final based on a large sql query.

I can't seem to figure out how to make a cursor or loop work here.

This is the script I've got - it runs but doesn't do anything. When i run the internal cursor only it takes the amount of rows (6) and enters each row 6 times. Not what I want.

Newly updated script:

DECLARE @Iteration INT = 1      -- Loop
DECLARE @diff INT  = 1          -- Cursor
DECLARE @owner_customer_no INT  -- Cursor

BEGIN    
                DECLARE loop_cursor CURSOR FOR  
                SELECT owner_customer_no, diff 
                FROM #diff_temp 

                OPEN loop_cursor   
                FETCH NEXT FROM loop_cursor INTO @owner_customer_no, @diff   

                WHILE @@FETCH_STATUS = 0   
                BEGIN  

                SET @Iteration = 1
                WHILE @Iteration <= @diff
                BEGIN     
                        insert into #final
                        select distinct 
                        e.customer_no,                          

                        0 as guest_cust_no,                 
                        h.fname,
                        ...
                        where   e.campaign_no = 1119
                        and sc.coding_scenario = 2

                PRINT @Iteration
                PRINT @diff
                PRINT @owner_customer_no

                SET @Iteration = @Iteration + 1
                END

                FETCH NEXT FROM loop_cursor INTO @owner_customer_no, @diff   
                END   

                CLOSE loop_cursor   
                DEALLOCATE loop_cursor
END

This code is generating the following

(6 row(s) affected)
iteration 1
diff 4
customer 1

(6 row(s) affected)
iteration 2
diff 4
customer 1

(6 row(s) affected)
iteration 3
diff 4
customer 1

(6 row(s) affected)
iteration 4
diff 4
customer 1

(6 row(s) affected)
iteration 1
diff 2
customer 2

(6 row(s) affected)
iteration 2
diff 2
customer 2

Each iteration/loop is inserting 6 rows - what i want it to do is insert 1 row.

Solution

When you declare the variable @diff, its value is NULL.

Then you immediately try to use it in the loop:

DECLARE @diff INT, @owner_customer_no INT -- Cursor

WHILE @Iteration <= @diff

But since @Iteration is never "less than or equal to" NULL, the WHILE loop doesn't execute.

EDIT: Actually, you need to move this loop:

WHILE @Iteration <= @diff
BEGIN
...
SET @Iteration = @Iteration + 1

END

to INSIDE your cursor, so that it executes for each of the rows in #diff_temp.

EDIT 2:

After this line,

FETCH NEXT FROM loop_cursor INTO @owner_customer_no, @diff

Set @Iteration back to 1, so that the inner loop will execute correctly for the next row from #diff_temp.