title: "Invalid object name" when using global temp table in stored procedure
body: <p>I'm following several examples to use <code>global temp table</code> such as <a href="https://stackoverflow.com/a/43529268/2243398">this</a> &amp; <a href="https://www.sqlservercentral.com/Forums/Topic1255095-145-1.aspx" rel="nofollow noreferrer">this</a></p>
  
  <p>I have following query to get data into a <code>global temp table</code>:</p>
  
  <pre><code>DECLARE @SQL NVARCHAR(MAX)
  
  SET @SQL = N'SELECT * INTO ##tmpDraftTableData  
               FROM ' + @DraftTableName 
  
  EXEC sp_executesql @SQL
  </code></pre>
  
  <p>Note that the <code>@DraftTableName</code> is dynamic and come from different table with different structure, that's why I need to use <strong>dynamic query</strong>.</p>
  
  <p>And then I want to insert the data as obtain from above global <code>temp table</code> into another temp table to loop and process the data:</p>
  
  <pre><code>SELECT *
  INTO #tmpDraftTableData
  FROM ##tmpDraftTableData  -- ERROR: Invalid object name ##tmpDraftTableData
  </code></pre>
  
  <p>Seems like I can't use the global temp table with the error </p>
  
  <blockquote>
    <p>Invalid object name ##tmpDraftTableData</p>
  </blockquote>
  
  <p>unlike other sample code. </p>
  
  <p>What did I do wrong here?</p>