title: dynamic sql not working . Regular sql working
body: <p>I am creating two table and trying to do <code>select * into</code> using sql and dynamic sql . I am not sure why the same query in dynamic sql is throwing me error . Can you please help</p>
  
  <p>Note: I know I can use create table and insert into to make it work. But my question is why below is not working with dynamic sql</p>
  
  <pre><code>create table emp_info (emp_id int,sal float) 
  create table emp_sal(emp_id int,sal float)
  
  insert into emp_info values (1,1000)
  insert into emp_info values (2,1000)
  insert into emp_sal values (1,1000)
  insert into emp_sal values (2,1000)
  
  BEGIN   
     SELECT A.emp_id,A.sal  into  #EE_tmp
     FROM emp_info A, emp_sal B   
     WHERE A.emp_id = 1   AND A.emp_id = B.emp_id   
     select * from  #EE_tmp 
   END 
  </code></pre>
  
  <blockquote>
    <p>Result: emp_id    sal 1   1,000</p>
  </blockquote>
  
  <pre><code>BEGIN
      DECLARE @dsql varchar(10000)
      SET @dsql = ' SELECT A.emp_id,A.sal  into  #EE_tmp'
                   + ' FROM emp_info A, emp_sal B'
                   + '  WHERE A.emp_id = 1'
                   +'  AND A.emp_id = B.emp_id'    
                   EXECUTE(@dsql)      
                   select * from #EE_tmp
   END
  </code></pre>
  
  <p>Result:</p>
  
  <blockquote>
    <p>Error: #EE_tmp not found. Specify owner.objectname or use sp_help to
    check whether the object exists (sp_help may produce lots of output).</p>
  </blockquote>