Loop in SQL and increase one field each time

Create table for store raw data:

CREATE TABLE [dbo].[raw_data](
[ID] [int] IDENTITY(1,1) NOT NULL,
[first_data] [int] NULL,
[next_counts] [nvarchar](max) NULL,
PRIMARY KEY (ID) );

Create table for processed data:

CREATE TABLE [dbo].[SplittedAndProcessedData](
[ID] [int] IDENTITY(1,1) NOT NULL,
[count] [int] NULL,
PRIMARY KEY (ID) );

Now store raw data:

INSERT INTO [dbo].[raw_data]
(
     [raw_data].[first_data]
    ,[raw_data].[next_counts]
) VALUES
(
    8500   /*first_data*/
    ,'10,2,0,95,15' /*next_counts*/
)

I am turning a comma separated string into individual rows:

SELECT Splitted_Row.value
FROM [dbo].[raw_data]
CROSS APPLY STRING_SPLIT ([raw_data].[next_counts], ',') Splitted_Row

Like this:

value
10
2
0
95
15

I want to insert into SplittedAndProcessedData table using a loop, like this:

ID	count
1	8500
2	8510
3	8512
4	8512
5	8607
6	8622

Which means:

1. ID(1) = 8500 –first data
2. ID(2) = 8500 + 10 = 8510
3. ID(3) = 8510 + 2 = 8512
4. ID(4) = 8512 + 0 = 8512
5. ID(5) = 8512 + 95 = 8607
6. ID(6) = 8607 + 15 = 8622

Answer

You need a cumulative sum, but using STRING_SPLIT() to extract the substring by position is a tricky apporach. As is mentioned in the documentation, the output rows might be in any order and the order is not guaranteed to match the order of the substrings in the input string.

But you may try a JSON-based approach to parse the next_counts column. You need to transform the data into a valid JSON array (10,2,0,95,15 into [10,2,0,95,15]) and parse this array with OPENJSON(). The result from the OPENJSON() call is a table with columns key, value and type and the key column holds the 0-based index of the element in the specified array.

-- INSERT INTO SplittedAndProcessedData ([count])
SELECT 
   SUM(data) OVER (ORDER BY rn) AS [count]
FROM (
   SELECT -1 AS rn, first_data AS data
   FROM raw_data r
   UNION ALL
   SELECT CONVERT(int, j.[key]), TRY_CONVERT(int, j.value)
   FROM raw_data r
   CROSS APPLY OPENJSON(CONCAT('[', r.next_counts, ']')) j
) t  

Result (form the SELECT part of the statement):

count
-----
8500
8510
8512
8512
8607
8622