How can I make a loop in a loop in SQL, like a for-each loop?

Question

I poured a lot of questions and answers, but I can not find a solution that works for me. Here is what I am trying to do:

Say I have a table called "Shop" that stores store identifiers, store names, and annual sales for this store. The business wants each store to increase its sales by 50% over the next year and wants to calculate the monthly target sales that each store must achieve in order to increase the cost by 50%. For example, if you sold $ 1,000 last year at store number 5, your goal at the end of next year is $ 1,500. And to achieve this, the amount for each month should be approximately $ 1042, $ 1084, $ 1126 ... $ 1500.

What I'm trying to do is insert 12 entries for each store into the table "monthly fact plan". I try to select a store from the Store table, grab the annual sales amount, and then do a cycle inside, where I calculate the value of each month so that I can insert it into the “monthly fact plan” table, then select the next store from the store table ... and other things. As a C # developer, this is a very simple task that can be accomplished using a "for each" loop with a different "for" loop inside it.

But I cannot for the life of me figure out how to do this in SQL. I know about temporary tables, but I can’t figure out how to get a few rows inserted into the “monthly fact plan” table using entries from the Store table, and the values ​​calculated in the loop to determine the monthly plan values, I read some of the cursors but it seems that most people advise against them in the SQL community, so I am at a loss from this.

Answer 1

Sql . , . ( ) , , , . CURSOR.

@paqogomez :)

Answer 2

T-SQL , . : "salesTargetYourExample", 1042, 1084,... , "linearGrowthSales", (, 1500 , 1000 ) , , , ).

, .

-- Create the store table
create table dbo.Store
(
    storeId int not null primary key clustered,
    storeName varchar(100) not null,
    annualSales money not null
);

-- Populate the data in the store table
insert into dbo.Store(storeId,storeName,annualSales)
values (1, 'My first store', 2000),
    (5, 'Store number five', 1000),
    (6, 'The sixth store', 2500);


-- Get the sales targets for each store on a monthly basis
select s.storeId, s.storeName, months.mth,
    (s.annualSales * 0.5 * months.mth/12) + s.annualSales as salesTargetYourExample,
    s.annualSales * 1.5 * months.mth/12 as linearGrowthSales
from dbo.Store as s
cross apply
(
    values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12)
) as months(mth)

Answer 3

SQL, , . . SQL, . , -, - ( ), :

Declare @intCount as int
Declare @numberOfTimes as int

WHILE @intCount <= @numberoftimes

BEGIN

--Do something repetitive

END