Loop through an comma separated array to insert in a table SQL Server

Question

Basically this is what I want to achieve. I am receiving both UserId and ProductId as parameters on my stored procedure, which inserts those values into a table called UserProduct. The ProductId is supplied as a comma separated array parameter and the UserId as a single integer value.

The following is the result set sample of what I want to achieve after select statement from this table. Is this achievable? Thanks in advance.

UserId  ProductId
-------------------
817      7 
817      5
817     33
798      6
798     12
798      2
798      4
888      5
...    ...

*DON'T* do that! Use a TVP or *avoid* using a stored procedure and use an ORM to generate the statements. Or use SqlBulkCopy to bulk-insert records from the client. You gain nothing by using a stored procedure for this — Panagiotis Kanavos, Apr 19 '18 at 08:37
Possible duplicate of [T-SQL split string](https://stackoverflow.com/questions/10914576/t-sql-split-string) — Panagiotis Kanavos, Apr 19 '18 at 08:40

Fer · Accepted Answer · 2018-04-19T08:37:45.673

2

You can try below TSQL.

-- supposing @ProductID comma seperated values - varchar
DECLARE @ix INT
DECLARE @value varchar(25)

WHILE PATINDEX('%,%', @ProductId) <> 0 
BEGIN
     SELECT  @ix= PATINDEX('%,%',@ProductId)
     SELECT  @value= LEFT(@ProductId, @ix- 1)
     SELECT  @ProductId= STUFF(@ProductId, 1, @ix, '')

    INSERT INTO table (UserId,ProductId) VALUES(@UserId, @value)
END

In fact, you should consider taking productIDs as an array.

edited Apr 19 '18 at 08:37

answered Apr 19 '18 at 08:31

Fer

1,962
7
29
58

2

This is the slowest way possible to split strings. The fastest way (apart from SQLCLR) uses XML. SQL Server 2016 added STRING_SPLIT which is faster than all previous techniques – Panagiotis Kanavos Apr 19 '18 at 08:36

score 2 · Answer 2 · answered May 17 '20 at 07:35

Kindly find this..

DECLARE @valueList varchar(8000)
DECLARE @pos INT
DECLARE @len INT
DECLARE @value varchar(8000)

SET @valueList = 'aa,bb,cc,f,sduygfdctys,w,e,r,t,sd sdf sdf,yyy yyy yy,'

--the value list string must end with a comma ','
--so, if the last comma it's not there, the following IF will add a trailing comma to the value list
IF @valueList NOT LIKE '%,'
BEGIN
    set @valueList = @valueList + ','
END


set @pos = 0
set @len = 0

WHILE CHARINDEX(',', @valueList, @pos+1)>0
BEGIN
    set @len = CHARINDEX(',', @valueList, @pos+1) - @pos
    set @value = SUBSTRING(@valueList, @pos, @len)
    --SELECT @pos, @len, @value /*this is here for debugging*/

    PRINT @value
    --Here is you value
    --DO YOUR STUFF HERE
    --DO YOUR STUFF HERE
    --DO YOUR STUFF HERE
    --DO YOUR STUFF HERE
    --DO YOUR STUFF HERE

    set @pos = CHARINDEX(',', @valueList, @pos+@len) +1
END

This only works if there are no empty spaces. For example, 'aa,bb,cc,,f,sduygfdctys result in ,f instead of f — comfortablyNumb, Apr 09 '21 at 14:36

Loop through an comma separated array to insert in a table SQL Server

2 Answers2