title: Splitting delimited values in a SQL column into multiple rows
body: <p>I would really like some advice here, to give some background info I am working with inserting Message Tracking logs from Exchange 2007 into SQL. As we have millions upon millions of rows per day I am using a Bulk Insert statement to insert the data into a SQL table.</p>
  
  <p>In fact I actually Bulk Insert into a temp table and then from there I MERGE the data into the live table, this is for test parsing issues as certain fields otherwise have quotes and such around the values.</p>
  
  <p>This works well, with the exception of the fact that the recipient-address column is a delimited field seperated by a ; character, and it can be incredibly long sometimes as there can be many email recipients.</p>
  
  <p>I would like to take this column, and split the values into multiple rows which would then be inserted into another table. Problem is anything I am trying is either taking too long or not working the way I want.</p>
  
  <p>Take this example data:</p>
  
  <pre><code>message-id                                              recipient-address
  2D5E558D4B5A3D4F962DA5051EE364BE06CF37A3A5@Server.com   user1@domain1.com
  E52F650C53A275488552FFD49F98E9A6BEA1262E@Server.com     user2@domain2.com
  4fd70c47.4d600e0a.0a7b.ffff87e1@Server.com              user3@domain3.com;user4@domain4.com;user5@domain5.com
  </code></pre>
  
  <p>I would like this to be formatted as followed in my Recipients table:</p>
  
  <pre><code>message-id                                              recipient-address
  2D5E558D4B5A3D4F962DA5051EE364BE06CF37A3A5@Server.com   user1@domain1.com
  E52F650C53A275488552FFD49F98E9A6BEA1262E@Server.com     user2@domain2.com
  4fd70c47.4d600e0a.0a7b.ffff87e1@Server.com              user3@domain3.com
  4fd70c47.4d600e0a.0a7b.ffff87e1@Server.com              user4@domain4.com
  4fd70c47.4d600e0a.0a7b.ffff87e1@Server.com              user5@domain5.com
  </code></pre>
  
  <p>Does anyone have any ideas about how I can go about doing this?</p>
  
  <p>I know PowerShell pretty well, so I tried in that, but a foreach loop even on 28K records took forever to process, I need something that will run as quickly/efficiently as possible.</p>
  
  <p>Thanks!</p>