Hello ,
We need to insert data into sql. we have one string as input that has multiple rows. We need to create stored procedure for that.
Rows are separated via semicolon and columns are separated via comma. Please suggest sql code . Below is the example of string.
Local,Actual,No Pass Thru,2020,January,NO UNITS,No Position Type,Lake ,Main,General,HSP_InputValue,2587.84;Local,Actual,No Pass Thru,2020,January,NO UNITS,No Position Type, BEACH,Main,Rent,HSP_InputValue,4070.1
declare @s varchar(max) = 'Local,Actual,No Pass Thru,2020,January,NO UNITS,No Position Type,Lake ,Main,General,HSP_InputValue,2587.84;Local,Actual,No Pass Thru,2020,January,NO UNITS,No Position Type, BEACH,Main,Rent,HSP_InputValue,4070.1'
You can run this to separate each row
select * from string_split(@s, ';')
or this to split into data rows
select * from string_split(@s, ';')
cross apply string_split([value], ',')
what is string split. please send the code of it.
are you using Microsoft SQL Server 2016 and later?
SQL server 2014
use delimiter 8k function by Jeff Moden available on internet .. google search
for less than SQL Server 2016
Here's Jeff's function
CREATE FUNCTION [dbo].[DelimitedSplit8K]
--===== Define I/O parameters
(@pString VARCHAR(8000), @pDelimiter CHAR(1))
--WARNING!!! DO NOT USE MAX DATA-TYPES HERE! IT WILL KILL PERFORMANCE!
RETURNS TABLE WITH SCHEMABINDING AS
RETURN
--===== "Inline" CTE Driven "Tally Table" produces values from 1 up to 10,000...
-- enough to cover VARCHAR(8000)
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
), --10E+1 or 10 rows
E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
cteTally(N) AS (--==== This provides the "base" CTE and limits the number of rows right up front
-- for both a performance gain and prevention of accidental "overruns"
SELECT TOP (ISNULL(DATALENGTH(@pString),0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
),
cteStart(N1) AS (--==== This returns N+1 (starting position of each "element" just once for each delimiter)
SELECT 1 UNION ALL
SELECT t.N+1 FROM cteTally t WHERE SUBSTRING(@pString,t.N,1) = @pDelimiter
),
cteLen(N1,L1) AS(--==== Return start and length (for use in substring)
SELECT s.N1,
ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,s.N1),0)-s.N1,8000)
FROM cteStart s
)
--===== Do the actual split. The ISNULL/NULLIF combo handles the length for the final element when no delimiter is found.
SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY l.N1),
Item = SUBSTRING(@pString, l.N1, l.L1)
FROM cteLen l
;
go
declare @s varchar(max) = 'Local,Actual,No Pass Thru,2020,January,NO UNITS,No Position Type,Lake ,Main,General,HSP_InputValue,2587.84;Local,Actual,No Pass Thru,2020,January,NO UNITS,No Position Type, BEACH,Main,Rent,HSP_InputValue,4070.1'
select s.*, c.Item from [dbo].DelimitedSplit8K s
cross apply [dbo].DelimitedSplit8K c
Where is that string coming from? A file? If so, just use BULK INSERT with settings for the column terminator and row terminator.
from Web application, that;s why we creating a Stored procedure and take that as a string as INPUT (Varchar(MAX)).
Apologies for the late reply...
The DelimitedSplit8K code was designed to only handle up to VARCHAR(8000) and slows down quite a bit if converted to VARCHAR(MAX). That may still be what's needed to be done though. What's the total length of data that should actually be expected?
As an alternative, you fold might want to look into table valued parameters. I've not used them before but others have with some decent success. I was going to recommend sending JSON for parsing but I don't recall if that was available in 2014.
Huh?
Whacha mean, "Huh"? Haven't you ever seen a typo before? Of course it should be "8000" and not "80000". 
I fixed the post.