declare @Sql varchar(1024) = 'Select * into #myTable from dbo.Employee';This is simply because the temp table will be out of the scope once your exec finishes executing and returns control back to your session. What you can do, however, in order to preserve your original logic is something like this:
declare @Sql varchar(1024) = 'Select * into ##myTable from dbo.Employee'; exec (@Sql); -- you still have access to this data: select * from ##myTable;The above will insert data into global temp table, so you will still have it available. The downside is of course that it is available to other connections as well, which might be undesirable. And you should drop it of course once you are done with it.
1 Person is following this question.