title: Keep table downtime to a minimum by renaming old table, then filling a new version?
body: <p>I have a handful or so of permanent tables that need to be re-built on a nightly basis. </p>
  
  <p>In order to keep these tables "live" for as long as possible, and also to offer the possibility of having a backup of just the previous day's data, another developer vaguely suggested 
  taking a route similar to this when the nightly build happens: </p>
  
  <ol>
  <li><p>create a permanent table (a build version; e.g., tbl_build_Client)</p></li>
  <li><p>re-name the live table (tbl_Client gets re-named to tbl_Client_old)</p></li>
  <li><p>rename the build version to become the live version (tbl_build_Client gets re-named to tbl_Client)</p></li>
  </ol>
  
  <p>To rename the tables, sp_rename would be in use.<br>
  <a href="http://msdn.microsoft.com/en-us/library/ms188351.aspx" rel="noreferrer">http://msdn.microsoft.com/en-us/library/ms188351.aspx</a>  </p>
  
  <p>Do you see any more efficient ways to go about this, 
  or any serious pitfalls in the approach? Thanks in advance.</p>
  
  <hr>
  
  <p><strong>Update</strong> </p>
  
  <p>Trying to flush out gbn's answer and recommendation to use synonyms, 
  would this be a rational approach, or am I getting some part horribly wrong? </p>
  
  <p>Three real tables for "Client":<br>
  1.  dbo.build_Client<br>
  2.  dbo.hold_Client<br>
  3.  dbo.prev_Client</p>
  
  <p>Because "Client" is how other procs reference the "Client" data, the default synonym is  </p>
  
  <pre><code>CREATE SYNONYM         Client  
  FOR           dbo.hold_Client
  </code></pre>
  
  <p>Then take these steps to refresh data yet keep un-interrupted access.<br>
  (1.a.) <code>TRUNCATE dbo.prev_Client</code> (it had yesterday's data)<br>
  (1.b.) <code>INSERT INTO dbo.prev_Client</code> the records from dbo.build_Client, as dbo.build_Client still had yesterday's data</p>
  
  <p>(2.a.) <code>TRUNCATE dbo.build_Client</code><br>
  (2.b.)  <code>INSERT INTO dbo.build_Client</code> the new data build from the new data build process<br>
  (2.c.)  change the synonym</p>
  
  <pre><code>DROP SYNONYM           Client
  CREATE SYNONYM         Client  
  FOR          dbo.build_Client
  </code></pre>
  
  <p>(3.a.)  <code>TRUNCATE dbo.hold_Client</code><br>
  (3.b.)  <code>INSERT INTO dbo.hold_Client</code> the records from dbo.build_Client<br>
  (3.c.) change the synonym</p>
  
  <pre><code>DROP SYNONYM          Client
  CREATE SYNONYM        Client  
  FOR          dbo.hold_Client
  </code></pre>