title: Combine 2 tables in SQL with some different column names (Transact SQL)
body: <p>I'm trying to combine multiple tables in SQL Server 2012, where some columns are the same, but others are different. I want to have them in the same table, with NULL values where data is missing. </p>
  
  <p>What I want is something like union but allowing different column names.</p>
  
  <p>For example,</p>
  
  <pre><code>Table 1:  ID | First name | Middle Name | Surname
  Table 2:  ID | First name | Surname | NI
  </code></pre>
  
  <p>Would combine to produce:        </p>
  
  <pre><code>          ID | First name | Middle Name | Surname| NI   
  </code></pre>
  
  <p>with NULL values for NI for entries coming from Table 2, etc</p>
  
  <p>I want to avoid creating a new table and inserting the values from the other tables, as some of my tables have over 100 columns</p>