title: CREATE TABLE with Dynamic Column
body: <p>I need to insert some data into a temp table. </p>
  
  <p>I have some <strong><em>conditional based columns like <code>Salary</code>, <code>Code</code></em></strong>. </p>
  
  <p>How can I create a table for conditional based columns? <strong>I don't want to use</strong> 
  <strong><code>SELECT INTO #tempTable</code></strong></p>
  
  <p>Here is the code:</p>
  
  <pre><code>DECLARE @sql NVARCHAR(MAX)
  ,@sqlSelect NVARCHAR(MAX) = ''
  ,@sqlFrom NVARCHAR(MAX) =''
  
  CREATE TABLE #myTempTable (Id INT, DeptId INT, DeptName VARCHAR(100))
  
  
  SET @sqlSelect ='INSERT INTO #myTempTable
  SELECT EMP.Id, EMP.DeptId, EMP.DeptName'
  
  SET @sqlFrom =' FROM dbo.EMPLOYEE AS EMP'
  
  IF (someCondition)
  BEGIN
      SET @sqlSelect = @sqlSelect +', EMP.Salary, EMP.Code'            
  END
  
  SET @sql =  @sqlSelect +@sqlFrom 
  
  EXEC sp_executesql @sql
  </code></pre>
  
  <p>Any help/suggestion on how better i can make this?</p>
  
  <hr>
  
  <p><strong>Update:</strong></p>
  
  <p>Initially I used <code>SELECT INTO #TempTable</code> without specifying no of columns, As SQL Azure not supporting that, I decided to go with <code>INSERT INTO</code>. But not sure how can I add dynamic columns in a defined structure already. Its fully dynamic SQL :(</p>