title: Combine two SQL Server table results to pull latest data where exists
body: <p>I have two tables a main table and a work in progress table.  Any inserts/updates are inserted into the WIP table while the record is being manipulated, this allows for validation checks and the like.  I want to create a view that combines the two tables showing the WIP table data whenever it exists and the main table data when there is no WIP data.</p>
  
  <p>I have figured out a way to do this but it seems that it's not the most elegant solution.  I would like to know if there are other ideas or better solutions?</p>
  
  <p>Example illustrating the situation:</p>
  
  <pre><code>select mt.id, wt.id wip_id, isnull(wt.name,mt.name) name, 
         isnull(wt.address, mt.address) address
  from main_table mt full outer join
         wip_table wt on mt.id = wt.orig_id;
  </code></pre>
  
  <p>So that will pull results from the WIP table when they exist, if they dont it will pull results from the main table.  This was a simple example but the tables could have many rows.</p>