Dynamic Global Temp Table in Sql server

Question

I have a procedure that is creating a global temp table. the problem is when you use more than one user at the same time give a problem because I cancel my temp table at the beginning , is there is one help me on about how to create an global temp table according to the session.

Answer 1

1. If you truly want a "per session" table, then the names will have to be different (as they are global as their name implies), so use dynamic sql to generate an arbitrary name, perhaps based on spid:

   declare @sql nvarchar(max)
   select @sql = N'select * into tempdb..##TempStudentRegistration' + convert(nvarchar(10), @@spid) + ' from Students'
   exec sp_executesql @sql
   

(Note: Using Spids alone would assume the lifetime of the global temp table would be the same as the spid and that callers are not reusing spies; feel free to add as much additional uniqueness to that as your particular need dictates).

2. If instead, you want a single global table, but just support multiple sessions writing to it, then consider recreating the global temp table or a locking mechanism to create the table if it doesn't exist:

   declare @rc int
   begin tran
   
   exec @rc = sp_getapplock 'mygloballock', 'exclusive', 'transaction'
   if (@rc >= 0)
   begin
       -- got the lock, if the table doesn't exist, create it
       if object_id('tempdb..##TempStudentRegistration') is null
           select top 0 * into ##TempStudentRegistration from Student
   
       exec sp_releaseapplock 'mygloballock'
       commit tran
   
       insert into ##TempStudentRegistration
          select * from Student
   end
   else
   begin
       raiserror('you did not get the app lock', 16, 1)
   end     
   

3. Finally, if you don't actually need global temp tables (i.e., you don't need to access the data from another session), consider using local temp tables (with a single # only), which are already scoped to the current session for you:

   select * into #TempStudentRegistration From Students
   

If you're unsure about which type of temporary table you should be using, here's a great answer describing your options: https://stackoverflow.com/a/2921091/4313829