Combine 2 tables in SQL with some different column names (Transact SQL)
<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>