Databases
StackOverflow2013
Postsdbo
POSplitting delimited values in a SQL column into multiple rows
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSplitting delimited values in a SQL column into multiple rows
    text
    copied!<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>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload