title: insert data with comma delimiter but with different ID
body: <p>Hi I am having a problem with my <strong>bulk insertion.</strong><br/>
  The challenge/objective is to save a column with comma delimiter to another table with different ID's<br>
  like for example-><br>
  I selected query<br> from @temp table<br> </p>
  
  <blockquote>
    <p>Rolln &nbsp; &nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;FullName<Br>
    441246&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;John Doe, Jane More, John Spade<br>
    441248&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Jose Mendez, Ali Muhm, Jacob Stile<br></p>
  </blockquote>
  
  <p>to be save in People table<br>
  The result would be after insertion to People-><br></p>
  
  <blockquote>
    <p>People_ID &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;RollNumber&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;FullName<br>
    1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;441246&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; &nbsp;John Doe<br>
    2 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;441246&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; &nbsp;Jane More<br>
    3 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;441246&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; &nbsp;John Spade<br></p>
  </blockquote>
  
  <p>my script that I created is repeating the name <br></p>
  
  <pre><code>INSERT @Temp_Original
  select rollnumber,
  case when CHARINDEX(',',FullName)&gt;0 
       then SUBSTRING(FullName,1,CHARINDEX(',',FullName)-1) 
       else FullName end FullName, 
  CASE WHEN CHARINDEX(',',FullName)&gt;0 
       THEN REPLACE(SUBSTRING(FullName,CHARINDEX(',',FullName)+1,len(FullName)), ',', '') 
       ELSE NULL END as FullName2,
   CASE WHEN CHARINDEX(',',FullName)&gt;0 
       THEN REPLACE(SUBSTRING(FullName,CHARINDEX(',',FullName)+1,len(FullName)), ',', '')  
       ELSE NULL END as FullName3,
  [Address], Code, Place, [Country], LegalTxt
  from (
  select newrec.RollNumber, newrec.FullName, newrec.[Address], newrec.Code, newrec.Place, newrec.Country, newrec.LegalTxt
   from [dbo].[view_FinanceCompare] newrec
  left join [dbo].[view_MainCompare] oldrec
  on newrec.RollNumber = oldrec.rollnumber
  where  oldrec.rollnumber is null
  ) t
  
  
  
  --Print 'Insert to people'
  Insert [dbo].[dbo_People] ([FullName], [CreatedOn], [CreatedBy])
  Select Fullname, getdate(), rollnumber
  From @Temp_Original
  UNION 
  Select Fullname2, getdate(), rollnumber
  From @Temp_Original Where FullName2&lt;&gt;''
  UNION 
  Select Fullname3, getdate(), rollnumber
  From @Temp_Original Where FullName3&lt;&gt;''
  </code></pre>
  
  <p><br>
  Your helping hand is mostly appreciated.</p>