title: TSQL select into Temp table from dynamic sql
body: <p>This seems relatively simple, but apparently it's not.</p>
  
  <p>I need to create a temp table based on an existing table via the select into syntax:</p>
  
  <pre><code>SELECT * INTO #TEMPTABLE FROM EXISTING_TABLE
  </code></pre>
  
  <p>The problem is, the existing table name is accepted via a parameter...</p>
  
  <p>I can get the table's data via:</p>
  
  <pre><code>execute ('SELECT * FROM ' + @tableName)
  </code></pre>
  
  <p>but how do I marry the two so that I can put the results from the execute directly into the temp table.</p>
  
  <p>The columns for each table that this is going to be used for are not the same so building the temp table before getting the data is not practical.</p>
  
  <p>I'm open to any suggestions except using a global temp table.</p>
  
  <p><strong>Update:</strong></p>
  
  <p>This is completely ridiculous, BUT my reservations with the global temp table is that this is a multi user platform lends itself to issues if the table will linger for long periods of time...</p>
  
  <p>Sooo.. just to get past this part I've started by using the execute to generate a global temp table.</p>
  
  <pre><code>execute('select * into ##globalDynamicFormTable from ' + @tsFormTable) 
  </code></pre>
  
  <p>I then use the global temp table to load the local temp table:</p>
  
  <pre><code>select * into #tempTable from ##globalDynamicFormTable
  </code></pre>
  
  <p>I then drop the global table.</p>
  
  <pre><code>drop table ##globalDynamicFormTable
  </code></pre>
  
  <p>this is dirty and I don't like it, but for the time being, until i get a better solution, its going to have to work.</p>
  
  <p><strong>In the End:</strong></p>
  
  <p>I guess there is no way to get around it.  </p>
  
  <p>The best answer appears to be either;</p>
  
  <p>Create a <strong>view</strong> in the execute command and use that to load the local temp table in the stored procedure.</p>
  
  <p>Create a <strong>global temp table</strong> in the execute command and use that to load the local temp table.  </p>
  
  <p>With that said i'll probably just stick with the global temp table because creating and dropping views is audited in my organization, and I'm sure they are going to question that if it starts happening all the time.</p>
  
  <p>Thanks!</p>