I have a string which I need to insert into a table after extracting values out of it. An example string is like this:

111111,1111,11.11,'2019-01-01-11.11.11.111111'#222222,2222,22.22,'2019-02-02-22.22.22.222222'

Using string_split, I can get split the values in different rows:

declare @s varchar(1000)
set @s = '111111,1111,11.11,''2019-01-01-11.11.11.111111''#222222,2222,22.22,''2019-02-02-22.22.22.222222'''

select value from string_split(@s, '#')

--Output
--111111,1111,11.11,'2019-01-01-11.11.11.111111'
--222222,2222,22.22,'2019-02-02-22.22.22.222222'

It gets interesting.

The output needs to be inserted to a table. I have thought about dynamic SQL, but am not too fond of that:

declare @SQLstatment varchar(1000)
set @SQLstatment = 'insert into MyTable (Col1,Col2,Col3,Col4) values (' + OUTPUT_FROM_SPLIT + ')'
exec (@SQLstatment)

It gets more interesting.

Col4 is of DB2 data type timestamp. I need to convert it to SQL Server 2016 datetime2 data type using this statement I wrote:

declare @DB2_timpstamp varchar(30) = '2019-02-25-11.22.33.456789'
select convert(datetime2, stuff(stuff(stuff(@DB2_timpstamp, 17, 1, ':'), 14, 1, ':'), 11, 1, ' '))

Problem

I cannot make this all work.

I want to avoid using dynamic SQL as much as I can. Most probably I've to use loops. A solution will look like this:

while(SOME_CONDITION)
begin
 insert into MyTable (Col1,Col2,Col3,Col4) values
 (Val1,Val2,Val3,convert(datetime2, stuff(stuff(stuff(Val4, 17, 1, ':'), 14, 1, ':'), 11, 1, ' ')))
 LOOP_VARIABLE_INCREMENT
end

OK, not an expert on SQL Server convertion functions but this will get you in the right track:

with 
r (value) as ( -- raw varchar value
 select value from string_split(@s, '#')
),
x (col1, col2, col3, col4) as ( -- values in separate columns
 select 
 substring(value, 1, 10) as col1, -- fix conversion here
 substring(value, 11, 10) as col2, -- fix conversion here
 substring(value, 21, 10) as col3, -- fix conversion here
 substring(value, 31, 10) as col4, -- fix conversion here
 from r
)
insert into MyTable (Col1,Col2,Col3,Col4) -- insert now
select col1, col2, col3, col4 from x

As you see, there's no need for a loop. Just normal SQL will do. Of course, you'll need to tailor the conversion formulas a bit, but this will get you pretty close.

You need to split twice:

declare @s varchar(1000)
set @s = '111111,1111,11.11,''2019-01-01-11.11.11.111111''#222222,2222,22.22,''2019-02-02-22.22.22.222222'''

;WITH cte AS (
 select value
 from string_split(@s, '#')
)
SELECT
 MAX(CASE WHEN ord=1 THEN v END) AS col1,
 MAX(CASE WHEN ord=2 THEN v END) AS col2,
 MAX(CASE WHEN ord=3 THEN v END) AS col3,
 MAX(CASE WHEN ord=4 THEN v END) AS col4
FROM cte
CROSS APPLY (SELECT value AS v, ROW_NUMBER() OVER(ORDER BY 1/0) AS ord 
 FROM STRING_SPLIT([value], ',')) s
GROUP BY value;

db<>fiddle demo

Using the power of DelimitedSplit8k_Lead you can split the items twice, and know their ordinal position (very important for pivoting). Giving you a nice simple statement of:

DECLARE @DataSet varchar(MAX) = '111111,1111,11.11,''2019-01-01-11.11.11.111111''#222222,2222,22.22,''2019-02-02-22.22.22.222222''';
--INSERT INTO MyTable (Col1,Col2,Col3,Col4)
SELECT MAX(CASE WHEN C.ItemNumber = 1 THEN C.item END),
 MAX(CASE WHEN C.ItemNumber = 2 THEN C.item END),
 MAX(CASE WHEN C.ItemNumber = 3 THEN C.item END),
 MAX(CASE WHEN C.ItemNumber = 4 THEN TRY_CONVERT(Datetime2(6),STUFF(STUFF(STUFF(C.item,17,1,':'),14,1,':'),11,1,'T')) END)
FROM dbo.DelimitedSplit8K_lead(@DataSet,'#') R
 CROSS APPLY dbo.DelimitedSplit8K_lead (REPLACE(R.item,'''',''),',') C --Because we don't want those nasty single quotes!
GROUP BY R.item;

Here's a solution that splits the string twice, and preserves the order of the columns even if they are variable length. I used this trick to preserve the order of the values returned from STRING_SPLIT. I did not preserve the order of the "rows" in the original string since it does not matter what order they are inserted in.

DECLARE @s VARCHAR(1000) = '111111,1111,11.11,''2019-01-01-11.11.11.111111''#222222,2222,22.22,''2019-02-02-22.22.22.222222''';
DECLARE @c VARCHAR(1) = ','
;WITH CTE AS 
(
 SELECT s.rowNum, t.value, t.columnNum
 FROM 
 (
 SELECT s.value, ROW_NUMBER() OVER(ORDER BY s.value) AS rowNum
 FROM STRING_SPLIT(@s, '#')s
 )s
 OUTER APPLY 
 (
 SELECT T.VALUE, ROW_NUMBER() OVER(ORDER BY CHARINDEX(@C + t.value + @C, @C + s.value + @C))AS columnNum
 FROM STRING_SPLIT(s.value, ',') t
 )t
)
 INSERT INTO myTable (col1, col2, col3, col4)) --do your conversions here
 SELECT MAX(CASE WHEN columnNum = 1 THEN value END), MAX(CASE WHEN columnNum = 2 THEN value END), MAX(CASE WHEN columnNum = 3 THEN value END), MAX(CASE WHEN columnNum = 4 THEN value END)
 FROM cte c
 GROUP BY rownum