sql server - Alternative to cursor pivot split function? -

so i'm making stored procedure end-goal being dynamic etl solution. company deals lot of third-party data , times not know number of columns, data types, format of data, etc... such, i've put number of temporary tables dynamic sql , bulk insert statements data sql server. currently, data comes single column nvarchar field tab or pipe separated, , there's upwards of 100k rows per txt or csv file. example of aforementioned csv/txt format below:

rawsingleline 9xx01 no cancelled inadvertent approval 1/12/2015 432115.2 99    480x1 no cancelled pending processing 1/7/2014 5060 27.5     

my current solution use cursor , below split function loop through single rows, split them, pivot them, , insert 1 of dynamic temporary tables. however, i'd avoid cursor because they're expensive, , set based operation preferred.

create function [dbo].[udf_split] (     @string nvarchar(4000),     @delimiter nchar(1) ) returns table return (     split(stpos,endpos)     as(         select 0 stpos, charindex(@delimiter,@string) endpos         union         select endpos+1, charindex(@delimiter,@string,endpos+1)             split             endpos > 0     )     select 'id' = row_number() on (order (select 1)),         'data' = substring(@string,stpos,coalesce(nullif(endpos,0),len(@string)+1)-stpos)     split ) 

is there way achieve want without cursor?

disclaimer: i'm owner of project eval sql.net

this solution allow split , pivot 100k rows in around 2-3 seconds. eval sql.net allow execute c# code in t-sql.

-- create big string rows (100,000 rows) declare @s varchar(max) = '' set @s = ( select top ( 100000 )                     rawsingleline + char(13) + char(10)                import xml path('') ,         type          ).value('text()[1]', 'varchar(max)')  -- use c# syntax split text. use regex.split if necessary. declare @sqlnet sqlnet = sqlnet::new(' var rows = s.split(new string[] { environment.newline }, stringsplitoptions.removeemptyentries); return rows.select(x => x.split('' '')).tolist() ').valuestring('s', @s).autodispose()  exec dbo.sqlnet_evalresultset @sqlnet