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.
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',',')
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
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