Two days ago, I wrote an article SQL SERVER – How to Create Table Variable and Temporary Table? It was a very basic article for beginners. However, to my surprise, I have got tremendous response to that blog post and also got quite a lot of feedback about the it. Out of all these one feedback which stood out was that I did not include discussion about Global Temporary Table. I agree that when I was writing this blog post I did not think of this, but it is never too late to write about any topic. Let us see in this blog post how to create a global temporary table?
Global Temporary Table
They are exactly the same structure as a temporary table. They are created in tempDB. There is one big difference between Global Temporary Table and a regular temporary table. The Global Temporary table can be created in any session and they can be accessed in any other session as well. Additionally, you can create them outside the scope of the stored procedure and access them inside the stored procedure. You can create and drop them in any session.
In another simple words, they serve as a temporary table which is available across multiple sessions. You can either drop them by explicit DROP command or when SQL Server services are restarted.
Let us see how we can create a global temporary table.
CREATE TABLE ##TempTable (ID INT IDENTITY(1,1))
You can query the table just like a regular table by writing select statement from any new season as well.
Now, open a new query window in SSMS and type following command.
SELECT * FROM ##TempTable
You can see that you see that you can access this table from any session and it will return you empty results.
The table will be automatically dropped when you close the connection. If you want to explicitly drop the table you can execute the following command.
DROP TABLE ##TempTable
Just remember, any table which is created with ## in the beginning is a temporary table and it is created in the temp database.
Reference: Pinal Dave (https://blog.sqlauthority.com)