Home Forums SQL Server 7,2000 T-SQL INSERTING IN TEMP TABLE THRO DYNAMIC SQL RE: INSERTING IN TEMP TABLE THRO DYNAMIC SQL

  • When you create a temporary table it is in scope for either the duration of the batch (Local Temporary Table) or for the duration of the connection (Global Temporary Table.)

    A Local Temporary Table is identified by a single pound sign (#TempTable).

    A Global Temporary Table is identified by a double pound sign (##TempTable).

    Since dynamic SQL is, by definition, inside the scope of the batch it can see/access any temporary table available to the batch.

    If a stored procedure is called from a batch and the stored procedure executed Dynamic SQL then both the stored procedure and the dynamic SQL have access to the temporary table.

    What I see missing is what Frank alluded to with the Create Table Statement.  The temporary table must exist within the scope of the Batch/Stored Procedure for the Dynamic SQL to see/access it.

    This does not work in reverse for Local Temporary Tables though.  If you create a Local Temporary Table (#) inside the Dynamic SQL, as soon as the Dynamic SQL completes the temporary table is out-of-scope and removed from tempdb.

    I hope this helps.