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.

Answer 1

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 @MyList 
          ORDER BY 1 OFFSET @COUNTER 
          ROWS FETCH NEXT 1 ROWS ONLY);

PRINT @VALUE

SET @COUNTER = @COUNTER + 1

END

You get the same result

Iterate through a list of strings In SQL Server?

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

Sign up

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