title: I need to split string in select statement and insert to table
body: <p>I have a data in one table. I need to copy it to another table. One of the column is text delimited string. So what I'm thinking to select all columns insert get indentity value and with subquery to split based on delimiter and insert it to another table.</p>
  
  <p>Here is the data example</p>
  
  <pre><code>ID     Name     City       Items
  1      Michael  Miami      item|item2|item3|item4|item5
  2      Jorge    Hallandale item|item2|item3|item4|item5
  </code></pre>
  
  <p>copy <strong>Name</strong>, <strong>City</strong> to one table get identity
  and split and copy <strong>Items</strong> to another table with Identity Column Value</p>
  
  <p>So output should be</p>
  
  <p>Users table</p>
  
  <pre><code>UserID Name     City
  1      Michael  Miami
  2      Jorge    Hallandale
  </code></pre>
  
  <p>...</p>
  
  <pre><code>Items table
  
  ItemID   UserID   Name
  1        1        Item
  2        1        Item2
  3        1        Item3
  4        1        Item4
  5        2        Item
  6        2        Item2
  7        2        Item3
  8        2        Item4
  </code></pre>
  
  <p>Not really sure how to do it with T-SQL. Answers with examples would be appreciated</p>