title: What's the best way to update data in a table while it's in use without locking the table?
body: <p>I have a table in a SQL Server 2005 Database that is used a lot. It has our product on hand availability information. We get updates every hour from our warehouse and for the past few years we've been running a routine that truncates the table and updates the information. This only takes a few seconds and has not been a problem, until now. We have a lot more people using our systems that query this information now, and as a result we're seeing a lot of timeouts due to blocking processes. </p>
  
  <p>... so ...</p>
  
  <p>We researched our options and have come up with an idea to mitigate the problem.</p>
  
  <ol>
  <li>We would have two tables. Table A (active) and table B (inactive).</li>
  <li>We would create a view that points to the active table (table A).</li>
  <li>All things needing this tables information (4 objects) would now have to go through the view.</li>
  <li>The hourly routine would truncate the inactive table, update it with the latest information then update the view to point at the inactive table, making it the active one.</li>
  <li>This routine would determine which table is active and basically switch the view between them.</li>
  </ol>
  
  <p>What's wrong with this? Will switching the view mid query cause problems? Can this work?</p>
  
  <p>Thank you for your expertise.</p>
  
  <p><em>Extra Information</em></p>
  
  <ul>
  <li><p>the routine is a SSIS package that peforms many steps and eventually truncates/updates the table in question</p></li>
  <li><p>The blocking processes are two other stored procedures that query this table.</p></li>
  </ul>