SQL Server Global Temporary Table Locking

Question

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.

And why is that a reason for a global temp table? – Lamak Jul 10 '14 at 21:22 — Lamak, Jul 10 '14 at 21:22
I too am confused why that requires a global temp table. – Sean Lange Jul 10 '14 at 21:28 — Sean Lange, Jul 10 '14 at 21:28

score 1 · Accepted Answer · answered Jul 10 '14 at 21:19

1

Why not include it inside a transaction block like

begin transaction
SELECT * 
INTO ##TempEmployee   
FROM Employee

answered Jul 10 '14 at 21:19

Rahul

76,197
13
71
125

score 0 · Answer 2 · answered Jul 11 '14 at 05:36

0

Try this,

WorkDummySQL

  create table rr(id integer,name varchar(20))
    insert into rr values(1,'aa')
    select * from rr

Tempdb

select * into ##ta from WorkDummySQL.dbo.rr

answered Jul 11 '14 at 05:36

Vinoth_S

1,380
1
12
15

SQL Server Global Temporary Table Locking

2 Answers2