Search code examples
sqlsql-serverglobal-temp-tables

SQL Server Global Temporary Table Locking

How do I lock a global temporary table in a stored procedure that's getting created and populated by a SELECT INTO statement? For example:

SELECT * 
INTO ##TempEmployee   
FROM Employee

This stored procedure is executed for generating reports and it's there in every client database (multi-tenant architecture using different DB per client). I do not want data in this global temporary table to be shared between clients when the report is generated concurrently. I don't have a choice but to use global temp table because I use it for generating columns on the fly using PIVOT.

Solution

  • Why not include it inside a transaction block like

    begin transaction
SELECT * 
INTO ##TempEmployee   
FROM Employee