Iterate through a list of strings In SQL Server?

Question

How do you access the value within a list in T-SQL?

I have a SQL statement that loops through and counts how many times a value appears in a specific column in Table_1. Then it inserts the required values into their columns on Table_2 or if the row dose not exist, it adds a new row and adds the necessary data.

I created the list or table to be exact,

DECLARE @MyList TABLE (Value NVARCHAR(50))
INSERT INTO @MyList VALUES ('Data1')
INSERT INTO @MyList VALUES ('Data2')
INSERT INTO @MyList VALUES ('Data3')
INSERT INTO @MyList VALUES ('Data4')
INSERT INTO @MyList VALUES ('Data5')
INSERT INTO @MyList VALUES ('Data6')
INSERT INTO @MyList VALUES ('Data7')
INSERT INTO @MyList VALUES ('Data8')

The statement works fine, but I have everything hardcoded and I want to add some dynamic data to be inserted, so I created a list of values (Strings). Now I can't access the values the way I though I could.

This is the whole statement,

DECLARE @cnt INT = 1;
DECLARE @MyList TABLE (Value NVARCHAR(50))
INSERT INTO @MyList VALUES ('Data1')
INSERT INTO @MyList VALUES ('Data2')
INSERT INTO @MyList VALUES ('Data3')
INSERT INTO @MyList VALUES ('Data4')
INSERT INTO @MyList VALUES ('Data5')
INSERT INTO @MyList VALUES ('Data6')
INSERT INTO @MyList VALUES ('Data7')
INSERT INTO @MyList VALUES ('Data8')

                        WHILE @cnt < 9
                        BEGIN

                            IF EXISTS (SELECT * FROM Staff_Manager.dbo.Staff_Count_TBL WHERE Staff_No = 3201 AND Year_D = 2016 AND Month_D = 6 AND Column_Index = @cnt)
                                BEGIN
                                  UPDATE Staff_Manager.dbo.Staff_Count_TBL 
                                            SET Column_Value = (
                                                 SELECT COUNT(*)  
                                                 FROM Staff_Manager.dbo.Staff_Time_TBL
                                                 WHERE Staff_No = 3201 AND Date_Data BETWEEN '2016/6/1' AND '2016/7/1' AND Info_Data = 'Data_1'
                                                 GROUP BY Staff_No, Info_Data),
                                                 Column_Value2 = 'Data1'
                                                 WHERE Staff_No = 3201 AND Year_D = 2016 AND Month_D = 6 AND Column_Index = @cnt
                                END
                            ELSE
                                BEGIN
                                   INSERT INTO Staff_Manager.dbo.Staff_Count_TBL 
                                        (Staff_No, Year_D, Month_D, Column_Index, Column_Value, Column_Value2)
                                        SELECT 3201, 2016, 6, @cnt, COUNT(*), 'Data1' 
                                             FROM Staff_Manager.dbo.Staff_Time_TBL
                                             WHERE Staff_No = 3201 AND Date_Data BETWEEN '2016/6/1' AND '2016/7/1' AND Info_Data = 'Data1' 
                                             GROUP BY Staff_No, Info_Data
                                END
                            SET @cnt = @cnt + 1
                        END

What I am trying to achieve is to loop through a list that has 8 items in it and enter those values into their corrosponding columns.

Eg,

On this line I have hardcoded Data1,

WHERE Staff_No = 3201 AND Date_Data 
       BETWEEN '2016/6/1' AND '2016/7/1' AND Info_Data = 'Data1'

What I am trying to do is this,

WHERE Staff_No = 3201 AND Date_Data 
       BETWEEN '2016/6/1' AND '2016/7/1' AND Info_Data = @MyList[@cnt]

And this,

SELECT 3201, 2016, 6, @cnt, COUNT(*), @MyList[@cnt] 
                     FROM Staff_Manager.dbo.Staff_Time_TBL                           
                     WHERE Staff_No = 3201 AND Date_Data 
                    BETWEEN '2016/6/1' AND '2016/7/1' AND Info_Data = @MyList[@cnt]

But that does not work. After researching a little more, I found that T-SQL does not actually make a list , but a temp Table so to speak and you need to get the value from there. Unfortunately I can't seem to get anything to work.

I have an UPDATE and a INSERT statement that I need to add the value from the list.

EDIT: Last minute code tweaking,

DECLARE @cnt INT = 1;
DECLARE @MyList TABLE (Value NVARCHAR(50))
INSERT INTO @MyList VALUES ('Data1')
INSERT INTO @MyList VALUES ('Data2')
INSERT INTO @MyList VALUES ('Data3')
INSERT INTO @MyList VALUES ('Data4')
INSERT INTO @MyList VALUES ('Data5')
INSERT INTO @MyList VALUES ('Data6')
INSERT INTO @MyList VALUES ('Data7')
INSERT INTO @MyList VALUES ('Data8') 
INSERT INTO @MyList VALUES ('Data9')
INSERT INTO @MyList VALUES ('Data10')
INSERT INTO @MyList VALUES ('Data11')
INSERT INTO @MyList VALUES ('Data12')
INSERT INTO @MyList VALUES ('Data13')
INSERT INTO @MyList VALUES ('Data14')
INSERT INTO @MyList VALUES ('Data15')
INSERT INTO @MyList VALUES ('Data16')
INSERT INTO @MyList VALUES ('Data17')
INSERT INTO @MyList VALUES ('Data18')
INSERT INTO @MyList VALUES ('Data19')
INSERT INTO @MyList VALUES ('Data20')
INSERT INTO @MyList VALUES ('Data21')
INSERT INTO @MyList VALUES ('Data22')
INSERT INTO @MyList VALUES ('Data23')
INSERT INTO @MyList VALUES ('Data24')
INSERT INTO @MyList VALUES ('Data25')
INSERT INTO @MyList VALUES ('Data26')
INSERT INTO @MyList VALUES ('Data27')
INSERT INTO @MyList VALUES ('Data28')
INSERT INTO @MyList VALUES ('Data29')
INSERT INTO @MyList VALUES ('Data30')

DECLARE @COUNTER INT = 0;
DECLARE @MAX INT = (SELECT COUNT(*) FROM @MyList)
DECLARE @VALUE VARCHAR(50);


                        WHILE @cnt <= @MAX  
                        BEGIN
                        SET @VALUE = (SELECT Value FROM @MyList 
                            ORDER BY 1 OFFSET @COUNTER 
                            ROWS FETCH NEXT 1 ROWS ONLY);


                            PRINT @cnt
                            PRINT @VALUE
                            PRINT @COUNTER

                            IF EXISTS (SELECT * FROM Staff_Manager.dbo.Staff_Count_TBL WHERE Staff_No = 3005 AND Year_D = 2016 AND Month_D = 6 AND Column_Index = @cnt)
                                BEGIN
                                  UPDATE Staff_Manager.dbo.Staff_Count_TBL 
                                            SET Column_Value = (
                                                 SELECT COUNT(*)  
                                                 FROM Staff_Manager.dbo.Staff_Time_TBL
                                                 WHERE Staff_No = 3005 AND Date_Data BETWEEN '2016/6/1' AND '2016/7/1' AND Info_Data = @value
                                                 GROUP BY Staff_No, Info_Data),
                                                 Column_Value2 = @value
                                                 WHERE Staff_No = 3005 AND Year_D = 2016 AND Month_D = 6 AND Column_Index = @cnt
                                END
                            ELSE
                                BEGIN
                                   INSERT INTO Staff_Manager.dbo.Staff_Count_TBL 
                                        (Staff_No, Year_D, Month_D, Column_Index, Column_Value, Column_Value2)
                                        SELECT 3005, 2016, 6, @cnt, COUNT(*), @value 
                                             FROM Staff_Manager.dbo.Staff_Time_TBL
                                             WHERE Staff_No = 3005 AND Date_Data BETWEEN '2016/6/1' AND '2016/7/1' AND Info_Data = @value 
                                             GROUP BY Staff_No, Info_Data
                                END
                            SET @cnt = @cnt + 1
                            SET @COUNTER = @COUNTER + 1
                        END

Am I correct that you are inserting same values eight times? The only difference is column_index. — IVNSTN
– IVNSTN, Commented Jul 15, 2016 at 5:04
@IvanStarostin, When column_index = 1 then 'Info_Data = Data1` , column_index = 2 then 'Info_Data = Data2` and so on through the loop. — KyloRen
– KyloRen, Commented Jul 15, 2016 at 5:11
Well, you don't have this algorithm in your code. Is this @MyList variable - from your real code or you are obtaining data to be insterted somehow else? If it is then how are you determining which row from @MyList belongs to which column? You don't have nor identity neither col_index column in @MyList so values are not linked to any position. — IVNSTN
– IVNSTN, Commented Jul 15, 2016 at 5:14
@Yes it is the actual code, of coarse, it is not working. So I should add an index of some sort to keep track of the position? — KyloRen
– KyloRen, Commented Jul 15, 2016 at 5:52
And after adding that col_index - have another look on your code. Does not this "loop" look like a regular join? on col_index=col_index, set Column_Value2 = data — IVNSTN
– IVNSTN, Commented Jul 15, 2016 at 5:57

Zein Makki · Accepted Answer · 2016-07-15 05:57:28Z

60

What you created is not a list but a table variable. So how to Iterate over a table. Below is a simple example and I think you can proceed after if you understand it:

(Note: Cursors are not efficient when it comes to performance and large tables)

DECLARE @MyList TABLE (Value NVARCHAR(50))
INSERT INTO @MyList VALUES ('Data1')
INSERT INTO @MyList VALUES ('Data2')
INSERT INTO @MyList VALUES ('Data3')
INSERT INTO @MyList VALUES ('Data4')
INSERT INTO @MyList VALUES ('Data5')
INSERT INTO @MyList VALUES ('Data6')
INSERT INTO @MyList VALUES ('Data7')
INSERT INTO @MyList VALUES ('Data8')

DECLARE @value VARCHAR(50)

DECLARE db_cursor CURSOR FOR  
SELECT Value FROM @MyList
OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @value   

WHILE @@FETCH_STATUS = 0   
BEGIN   
       PRINT @value

       -- PUT YOUR LOGIC HERE
       -- MAKE USE OR VARIABLE @value wich is Data1, Data2, etc...

       FETCH NEXT FROM db_cursor INTO @value   
END   

CLOSE db_cursor   
DEALLOCATE db_cursor

Prints:

Data1
Data2
Data3
Data4
Data5
Data6
Data7
Data8

So you have inside @value variable Data, Data2 .. etc . I think this solves your problem.

Alternative way is using a `WHILE` loop + `OFFSET` + `FETCH NEXT` :

DECLARE @MyList TABLE (Value NVARCHAR(50))
INSERT INTO @MyList VALUES ('Data1')
INSERT INTO @MyList VALUES ('Data2')
INSERT INTO @MyList VALUES ('Data3')
INSERT INTO @MyList VALUES ('Data4')
INSERT INTO @MyList VALUES ('Data5')
INSERT INTO @MyList VALUES ('Data6')
INSERT INTO @MyList VALUES ('Data7')
INSERT INTO @MyList VALUES ('Data8')

DECLARE @COUNTER INT = 0;
DECLARE @MAX INT = (SELECT COUNT(*) FROM @MyList)
DECLARE @VALUE VARCHAR(50);

WHILE @COUNTER < @MAX
BEGIN

SET @VALUE = (SELECT VALUE FROM
      (SELECT (ROW_NUMBER() OVER (ORDER BY (SELECT NULL))) [index] , Value from @MyList) R 
       ORDER BY R.[index] OFFSET @COUNTER 
       ROWS FETCH NEXT 1 ROWS ONLY);

PRINT @VALUE

SET @COUNTER = @COUNTER + 1

END

You get the same result

edited Jul 15, 2016 at 5:57

answered Jul 15, 2016 at 5:09

Zein Makki

30.2k6 gold badges58 silver badges66 bronze badges

Sign up to request clarification or add additional context in comments.

15 Comments

KyloRen Over a year ago

OK, so how do I put it in as a value in a column, Ie, This is hard coded Info_Data = Data1 , I want the data from the table when looping through, Info_Data = @MyList[@cnt

Zein Makki Over a year ago

@KyloRen @value variable is the same as @MyList[@cnt] that you want.

KyloRen Over a year ago

Ahh, I see what you did. You put the value into @Value. Will it always loop through in order of the table list?

KyloRen Over a year ago

Thanks, that has solved the issue. The second set of code is what I got working thanks again. UV for the help and nice answer.

KyloRen Over a year ago

OK, we have a problem Houston. The @Value does not iterate though the table list in order. It jumps all over the place. Why would this happen?

|

robotik · Accepted Answer · 2023-06-23 17:55:30Z

5

not with this specific code, but in general if you need data from a list of unique values in ascending (or descending) order, you don't need a cursor (or even a counter), you can select min/max Value from @MyList that is bigger/smaller than the last one. for a loop without counter the code looks like this:

SET @VALUE = ''
WHILE (@VALUE IS NOT NULL) BEGIN
  SET @VALUE = (SELECT MIN(Value) FROM @MyList WHERE Value > @VALUE)
  IF @VALUE IS NOT NULL BEGIN
    -- code comes here
  END
END

if you need the data in a specific order, you can add the counter to @MyList, and have a similar query with the counter in the while and where part (works with your code, too)

DECLARE @MyList TABLE (Cnt INT, Value NVARCHAR(50))
INSERT INTO @MyList VALUES (1, 'Data1')
INSERT INTO @MyList VALUES (2, 'Data2')
-- and so on

SET @cnt = (select min(Cnt) from @MyList)
WHILE (@cnt <= (select max(Cnt) from @MyList)) BEGIN
  SET @VALUE = (SELECT Value FROM @MyList WHERE Cnt = @cnt)
  -- your code comes here
  SET @cnt = @cnt + 1
END

edited Jun 23, 2023 at 17:55

answered Jun 14, 2022 at 9:53

robotik

2,0271 gold badge22 silver badges26 bronze badges

3 Comments

robotik Over a year ago

SELECT @VALUE prevents @VALUE to be NULL thus causing an infinite loop in the first code. updated my answer to use SET @VALUE to allow NULL

Andrew Richesson Over a year ago

Doesn't this cause an issue in the last iteration where @VALUE will be null since there is no more minimum values?

robotik Over a year ago

@AndrewRichesson very true, you need to handle NULL in the code, I updated the answer. Also, the first answer is very generic, and does not work with the OP's code since that uses @cnt many ways

Collectives™ on Stack Overflow

Iterate through a list of strings In SQL Server?

2 Answers 2

Alternative way is using a `WHILE` loop + `OFFSET` + `FETCH NEXT` :

15 Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Alternative way is using a WHILE loop + OFFSET + FETCH NEXT :

15 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related

Alternative way is using a `WHILE` loop + `OFFSET` + `FETCH NEXT` :