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
– Panagiotis Kanavos, Commented Apr 19, 2018 at 8:37

Fer · Accepted Answer · 2018-04-19 08:37:45Z

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, 2018 at 8:37

answered Apr 19, 2018 at 8:31

Fer

2,0028 gold badges32 silver badges58 bronze badges

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

1 Comment

Panagiotis Kanavos Over a year ago

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

Sheriff · Accepted Answer · 2020-05-17 07:35:34Z

2

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

answered May 17, 2020 at 7:35

Sheriff

8762 gold badges11 silver badges21 bronze badges

1 Comment

comfortablyNumb Over a year ago

This only works if there are no empty spaces. For example, 'aa,bb,cc,,f,sduygfdctys result in ,f instead of f

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related