Alex P Alex P - 2 months ago 14
SQL Question

MS SQL update statement using comma delimited values

I'm trying to create a SQL Update statement that will read comma separated values in a text box field & update a table (1 column referencing ID) with these comma separated values.

Specifically I have a table of products that I want to create a form that updates the "Inventory" column in that table.

I update my inventory spreadsheet daily and it would be great if I can copy/paste the CSV of inventory for all my products paste into a textbox hit update and have my table actualized.

I'm not sure how to do this as I need to reference each products unique ID and update that ID with the matching value for inventory stock.

Any ideas, examples highly appreciated.

Answer

If you are using SQL Server 2016 then you can use string_split as I stated in the comment above

select * from string_split('1,2,3,4,5',',')

However:

If you are using earlier version of sql server < 2016 then you can create a function that returns a table then from there you can join to the table you need to update:

CREATE FUNCTION dbo.SplitStringToValues
(
   @List NVARCHAR(MAX),
   @Delimiter NVARCHAR(255)
)
RETURNS TABLE
WITH SCHEMABINDING AS
RETURN
  WITH E1(N)        AS ( SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 
                         UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 
                         UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1),
       E2(N)        AS (SELECT 1 FROM E1 a, E1 b),
       E4(N)        AS (SELECT 1 FROM E2 a, E2 b),
       E42(N)       AS (SELECT 1 FROM E4 a, E2 b),
       cteTally(N)  AS (SELECT 0 UNION ALL SELECT TOP (DATALENGTH(ISNULL(@List,1))) 
                         ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E42),
       cteStart(N1) AS (SELECT t.N+1 FROM cteTally t
                         WHERE (SUBSTRING(@List,t.N,1) = @Delimiter OR t.N = 0))
  SELECT Item = SUBSTRING(@List, s.N1, ISNULL(NULLIF(CHARINDEX(@Delimiter,@List,s.N1),0)-s.N1,8000))
    FROM cteStart s;
go

Then use the function like so

SELECT * FROM SplitStringToValues('1,2,3,5',',') as t

UPDATE:

To get the next row's value next to the id as you have asked in the comment you could do something like this below. It is using the LEAD function introduced in SQL SERVER 2012:

SELECT Id, Value
FROM   (SELECT 
               ROW_NUMBER() over (order by(select 1)) as cnt,
               t.item AS Id, 
               Lead(t.item) 
                 OVER ( 
                   ORDER BY (SELECT 1)) Value 
        FROM   dbo.Splitstringtovalues('10,20,30,40,50,10,20,30,40,50,60,70', ',') 
               t) 
       keyValue 
WHERE  keyValue.value IS NOT NULL 
and cnt % 2 = 1