title: Insert query refinement - SQL Server
body: <p>I'm working with an ASP.NET MVC3 application and I have a database table for bus journeys which lists them with start and end dates, and the days of operation.  Here is a snippet below:</p>
  
  <pre><code>JourneyID   StartDate   EndDate     Monday  Tuesday Wednesday   etc
  676         02 Jan 2012 01 Mar 2012 True    False   True        etc
  696         02 Jan 2012 01 Mar 2012 False   False   True        etc
  650         02 Jan 2012 25 Mar 2012 True    True    True        etc
  </code></pre>
  
  <p>So if the date of 2 Jan 2012 is a Monday, and Monday is set to true, then there is a journey on that day.</p>
  
  <p>I'm trying to insert these into a new table row for each day that they run, so that would become:</p>
  
  <pre><code>JourneyID   Date
  676         02 Jan 2012
  676         04 Jan 2012
  696         04 Jan 2012
  650         02 Jan 2012
  650         03 Jan 2012
  </code></pre>
  
  <p>The query to do this that is currently on the system is quite slow - it uses nested while loops in t-sql. There was also a version in C# but that was slightly slower - although it did use a similar process (It used the nested loop process, then saved items to be inserted in a generic list, then bulk inserted them)</p>
  
  <p>The process in use currently loops through every journey, and within each journey, loops through every date between the start and end date to check what day it falls on and if that day is set to true. This can take a long time to perform.</p>
  
  <p>Can anyone think of a more efficient way to perform this insert?  It can currently loop through about 100,000 journeys an hour, but there could potentially be millions of journeys so it could take a long time.</p>