title: How to insert string_split output in a table?
body: <p>I have a string which I need to insert into a table after extracting values out of it. An example string is like this:</p>
  
  <pre><code>111111,1111,11.11,'2019-01-01-11.11.11.111111'#222222,2222,22.22,'2019-02-02-22.22.22.222222'
  </code></pre>
  
  <p>Using <code>string_split</code>, I can get split the values in different rows:</p>
  
  <pre><code>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'
  </code></pre>
  
  <p>It gets interesting.</p>
  
  <p>The output needs to be inserted to a table. I have thought about dynamic SQL, but am not too fond of that:</p>
  
  <pre><code>declare @SQLstatment varchar(1000)
  set @SQLstatment = 'insert into MyTable (Col1,Col2,Col3,Col4) values (' + OUTPUT_FROM_SPLIT + ')'
  exec (@SQLstatment)
  </code></pre>
  
  <p>It gets more interesting.</p>
  
  <p>Col4 is of DB2 data type <code>timestamp</code>. I need to convert it to SQL Server 2016 <code>datetime2</code> data type using this statement I wrote:</p>
  
  <pre><code>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, ' '))
  </code></pre>
  
  <h1>Problem</h1>
  
  <p>I cannot make this all work.</p>
  
  <p>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:</p>
  
  <pre><code>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
  </code></pre>