title: Using dynamic sql to populate a temp table
body: <p>I know this isn't ideal, but I would like to know if it is possible to populate a temp table based on dynamic sql? </p>
  
  <p>A very similar example to what I want to achieve is shown in <a href="https://stackoverflow.com/a/662093/685760">this answer</a> as either </p>
  
  <pre><code>SELECT into #T1 execute ('execute ' + @SQLString )
  </code></pre>
  
  <p>or </p>
  
  <pre><code>INSERT into #T1 execute ('execute ' + @SQLString )
  </code></pre>
  
  <p>I couldn't get either to work. It seems from the edit that the first option was wrong, so for the second option I've tried something like;</p>
  
  <pre><code>DECLARE @SQLString VARCHAR (2000) = 'SELECT * FROM INFORMATION_SCHEMA.COLUMNS'
  
  INSERT into #MyTempTable execute (@SQLString )
  </code></pre>
  
  <p>Any ideas are much appreciated.</p>
  
  <p><strong>Edit:</strong></p>
  
  <p>In an attempt to clarify what I am trying to do without being too localised, I explain as briefly as I can below.</p>
  
  <p>I have data in a staging area of my database that contains tables with dynamic names and a dynamic number of columns. However, a few of the column names are the same for each table. Rather than construct everything in dynamic sql, I would like to be able to simply extract the known columns into a temp table (or table variable, CTE, derived table or whatever) and act on that. </p>
  
  <p>So given a table as so;</p>
  
  <pre><code>  CREATE TABLE SomeParticularNameThatCantBeKnownToAStoredProc (
        [1] AS VARCHAR(100),
        [2] AS VARCHAR(100),
        ... -- Could be any number of these columns
        [Id] AS INT,
        [KnownCol] AS VARCHAR(100),
        [KnownCol2] AS VARCHAR(100),
        ....
        [DboId] AS INT
    )
  </code></pre>
  
  <p>I'd like to be able to perform the necessary operations to allow my to process this data without having to do it all in dynamic sql. I was hoping to be able to do something like;</p>
  
  <pre><code>  DECLARE @TableName AS VARCHAR(1000) = 'SomeParticularNameThatCantBeKnownToAStoredProc'
  
    SELECT [Id], [KnownCol], [KnownCol2], [DboId] 
    INTO #KnownName
    FROM @TableName -- I know this isn't possible, but this is what I'd like to do
  </code></pre>
  
  <p>This would then allow me to perform SQL statements against a consistent #KnownName. Some of the other operations I need to do are quite lengthy such as using the data to relate to other existing tables, copying data from the staging table(s) to their dbo schema equivalents and matching the DboId against the staging table Id using MERGE with OUTPUT INTO as described <a href="http://sqlblog.com/blogs/adam_machanic/archive/2009/08/24/dr-output-or-how-i-learned-to-stop-worrying-and-love-the-merge.aspx" rel="nofollow noreferrer">here</a>, and so on and so forth.</p>
  
  <p>If you can think of any other way I can limit the amount of dynamic SQL I need to write given the fact that the table name is dynamic then please let me know.</p>