Temporary tables existence during Dynamic SQL usage in SQL Server

Here is an interesting question posted in the forums here asking about:

Lots of time I face the problem with dynamic sql that I cant get the temporary table with data created through dynamic sql.

for instance:

SET NOCOUNT ON
DROP TABLE Employee
CREATE TABLE Employee(EmpId INT, EmpName VARCHAR(50), Sal    DECIMAL(13,2))
INSERT INTO Employee VALUES(1, 'Nishant', 30000)
INSERT INTO Employee VALUES(2, 'Ashish', 40000)
INSERT INTO Employee VALUES(3, 'Sushant', 60000)

EXEC('SELECT * INTO #Temp FROM Employee')

SELECT * FROM #Temp
DROP TABLE #Temp

Is there any way to get temporary table?

To cut long story short, as you may aware that the local temporary table that is created at one level of the code is not visible or dropped once the execution is finished, though the table gets created in the dynamic sql batch but you cannot query the table after the batch is executed.  You can create the temporary table in advance (before invoking the dynamic sql) and then insert in your dynamic sql. If the table structure is unknown in advance then you can still create it at the outer level with a dummy column, and then inside a dynamic sql batch alter and add the columns you need/drop the dummy column, followed by insert in another dynamic batch.

 For all the information about DSQL refer to The curse and blessings of dynamic SQL bible.