What is the clear purpose of Globle temporary tables?

Question

I have referred documents for Global temporary tables, but not understand the clear purpose of the temporary table.

Here is the URL which I have referred: https://www.c-sharpcorner.com/UploadFile/97fc7a/local-and-global-temporary-tables-in-sql-server-2008/

Could anyone help me to understand Global tables and the difference between Local and Global Temporary Tables? When would one use global temporary tables vs. local ones? Do they have a special purpose?

Answer 1

The difference between temp tables and global temp tables is their scope. This is described in the article you linked; temp tables are only visible from the stored procedure or session that created them and destroyed when that scope terminates. Global temp tables are available from anywhere, and are destroyed when no sessions reference them any longer.

My most common usage of global temp tables is when working with dynamic SQL. Because dynamic SQL is executed in a different scope than the query where it's defined (via sp_executesql), it can't access a temp table in the calling SQL from within the dynamic SQL.

Instead, I declare a global temp table then access that from within the dynamic SQL. This is usually in the context of collecting data from a number of databases at once, using a cursor to loop through all the databases and inserting into the global temp table.