title: SQL: How to dynamically loop & add N number of column with NULL value into temp table
body: <p>Due to a certain requirement, I need to create two temp tables in Stored Procedure, after processing some data into them, I need to combine the two temp tables to show as one result set and generate into excel. So I'm thinking to use <code>UNION</code> when I want to show the final result set.</p>
  
  <p>The issue is, the first temp table (Table A) is fixed to 20 columns, and the second temp table has 50 columns (Table B). My plan is, before processing data for Table A, I want to add 30 nullable columns and insert data for first 20 columns, and the rest is all <code>NULL</code></p>
  
  <p>After I process the data for Table B, I use <code>UNION</code> to combine Table A &amp; B so that they will show as one result set. </p>
  
  <p>What I can think of right now is to hard code some columns that are destined to have null values when I declare the temp table:</p>
  
  <pre><code>Declare @tmpTableA table (
    ....
    ProcessDate datetime,
    Mode int,
    Col21 varchar(10)
    Col22 varchar(10)
    ....
    Col50 varchar(50)
  )
  </code></pre>
  
  <p>When I insert data into Table A, I have to manually add null from Col21 onwards</p>
  
  <pre><code>Insert into(.... Col21, Col22, Col23....)
  Values (.... NULL, NULL, NULL....)
  </code></pre>
  
  <p>After I complete processing data for Table A &amp; B, I use <code>UNION</code> to merge Table A and B</p>
  
  <pre><code>Select *....Col49,Col50 From Table A
  Union
  Select *....CompleteDate,ContactPerson From Table B
  </code></pre>
  
  <p>Instead of hard-coding Col21 to Col50 into Table A, is there any elegant way to achieve that like using <code>while</code> loop to dynamically add N number of columns into Table A? </p>
  
  <p>EDIT:
  According to latest requirement, Table B has not only 50 columns but 100 columns! I really need a way to dynamically loop those columns rather than hard-coding for over 80 columns</p>