The two types of temporary tables are local and global temporary tables.<\/p>\n\n\n\n
Local Temporary Table<\/strong><\/h2>\n\n\n\nThe local temporary table has a pound sign (#) as the first character of the name. Creating a temp table is similar to creating a permanent table with a pound sign as a prefix in the table name. You can create the table using a CREATE TABLE<\/a> statement or SELECT INTO<\/a> statement. Let’s see an example to get the list of players belongs to India and store it in a temp table.<\/p>\n\n\n\nMethod 1<\/h3>\n\n\n\nCREATE TABLE #PlayerDetails \n(\n playerID INT, \n playerName VARCHAR(50), \n country VARCHAR(25)\n);\n\nINSERT INTO #PlayerDetails \nSELECT playerID, FirstName, countryID \nFROM tblPlayer \nWHERE countryID = 1 --India<\/pre>\n\n\n\nMethod 2 <\/h3>\n\n\n\nSELECT \n playerID, \n FirstName \nINTO #PlayerDetails \nFROM tblPlayer \nWHERE countryID = 1 --India<\/pre>\n\n\n\nAny one of the above queries creates the local temporary table ‘#PlayerDetails’ in ‘tempdb’.\u00a0To verify the created temp table in the server, expand ‘tempdb’ under system database in the object explorer window. See the below image.\u00a0\u00a0 <\/p>\n\n\n\n![\"Local](\"https:\/\/techieshouts.com\/wp-content\/uploads\/2019\/08\/image.png\")
Local temporary table<\/figcaption><\/figure>\n\n\n\nIn the above screen, notice that the temp table name has some values at the end. It is the unique identifier and SQL server generates it automatically. This is because the multiple database connections can create the same temp table. To differentiate the temp tables created by different connections, the unique identifier is appended at the end of the table name.\u00a0 <\/p>\n\n\n\n
The scope of the local temp table is available only for the connection that has created the table. You should perform the DDL\/ DML operations on the temp table in the same connection. When you close the connection that creates the temp table, it drops automatically. You can also drop the temp table manually by using DROP TABLE statement.<\/p>\n\n\n\n
Global Temporary Table<\/strong> <\/h2>\n\n\n\nThe global temporary table has two-pound signs (##) as the first character of the name.<\/p>\n\n\n\n
SELECT \n playerID, \n FirstName \nINTO ##PlayerDetails \nFROM tblPlayer \nWHERE countryID = 1 --India <\/pre>\n\n\n\nIn contrast to local temporary table, the scope of the global temp table is available to all the open connections. Since it can be accessed by all connections, no unique identifier is added at the end of the table name.<\/p>\n\n\n\n
When you close the last connection that references the temp table, it drops automatically. You can also drop the temp table manually by using DROP TABLE statement. <\/p>\n\n\n\n
Like permanent tables, temp tables can have constraints and indexes. <\/p>\n\n\n\n
Difference between local and global temp table<\/strong><\/h2>\n\n\n\n
Method 1<\/h3>\n\n\n\nCREATE TABLE #PlayerDetails \n(\n playerID INT, \n playerName VARCHAR(50), \n country VARCHAR(25)\n);\n\nINSERT INTO #PlayerDetails \nSELECT playerID, FirstName, countryID \nFROM tblPlayer \nWHERE countryID = 1 --India<\/pre>\n\n\n\nMethod 2 <\/h3>\n\n\n\nSELECT \n playerID, \n FirstName \nINTO #PlayerDetails \nFROM tblPlayer \nWHERE countryID = 1 --India<\/pre>\n\n\n\nAny one of the above queries creates the local temporary table ‘#PlayerDetails’ in ‘tempdb’.\u00a0To verify the created temp table in the server, expand ‘tempdb’ under system database in the object explorer window. See the below image.\u00a0\u00a0 <\/p>\n\n\n\nLocal temporary table<\/figcaption><\/figure>\n\n\n\nIn the above screen, notice that the temp table name has some values at the end. It is the unique identifier and SQL server generates it automatically. This is because the multiple database connections can create the same temp table. To differentiate the temp tables created by different connections, the unique identifier is appended at the end of the table name.\u00a0 <\/p>\n\n\n\n
The scope of the local temp table is available only for the connection that has created the table. You should perform the DDL\/ DML operations on the temp table in the same connection. When you close the connection that creates the temp table, it drops automatically. You can also drop the temp table manually by using DROP TABLE statement.<\/p>\n\n\n\n
Global Temporary Table<\/strong> <\/h2>\n\n\n\nThe global temporary table has two-pound signs (##) as the first character of the name.<\/p>\n\n\n\n
SELECT \n playerID, \n FirstName \nINTO ##PlayerDetails \nFROM tblPlayer \nWHERE countryID = 1 --India <\/pre>\n\n\n\nIn contrast to local temporary table, the scope of the global temp table is available to all the open connections. Since it can be accessed by all connections, no unique identifier is added at the end of the table name.<\/p>\n\n\n\n
When you close the last connection that references the temp table, it drops automatically. You can also drop the temp table manually by using DROP TABLE statement. <\/p>\n\n\n\n
Like permanent tables, temp tables can have constraints and indexes. <\/p>\n\n\n\n
Difference between local and global temp table<\/strong><\/h2>\n\n\n\n
Method 2 <\/h3>\n\n\n\nSELECT \n playerID, \n FirstName \nINTO #PlayerDetails \nFROM tblPlayer \nWHERE countryID = 1 --India<\/pre>\n\n\n\nAny one of the above queries creates the local temporary table ‘#PlayerDetails’ in ‘tempdb’.\u00a0To verify the created temp table in the server, expand ‘tempdb’ under system database in the object explorer window. See the below image.\u00a0\u00a0 <\/p>\n\n\n\nLocal temporary table<\/figcaption><\/figure>\n\n\n\nIn the above screen, notice that the temp table name has some values at the end. It is the unique identifier and SQL server generates it automatically. This is because the multiple database connections can create the same temp table. To differentiate the temp tables created by different connections, the unique identifier is appended at the end of the table name.\u00a0 <\/p>\n\n\n\n
The scope of the local temp table is available only for the connection that has created the table. You should perform the DDL\/ DML operations on the temp table in the same connection. When you close the connection that creates the temp table, it drops automatically. You can also drop the temp table manually by using DROP TABLE statement.<\/p>\n\n\n\n
Global Temporary Table<\/strong> <\/h2>\n\n\n\nThe global temporary table has two-pound signs (##) as the first character of the name.<\/p>\n\n\n\n
SELECT \n playerID, \n FirstName \nINTO ##PlayerDetails \nFROM tblPlayer \nWHERE countryID = 1 --India <\/pre>\n\n\n\nIn contrast to local temporary table, the scope of the global temp table is available to all the open connections. Since it can be accessed by all connections, no unique identifier is added at the end of the table name.<\/p>\n\n\n\n
When you close the last connection that references the temp table, it drops automatically. You can also drop the temp table manually by using DROP TABLE statement. <\/p>\n\n\n\n
Like permanent tables, temp tables can have constraints and indexes. <\/p>\n\n\n\n
Difference between local and global temp table<\/strong><\/h2>\n\n\n\n
Any one of the above queries creates the local temporary table ‘#PlayerDetails’ in ‘tempdb’.\u00a0To verify the created temp table in the server, expand ‘tempdb’ under system database in the object explorer window. See the below image.\u00a0\u00a0 <\/p>\n\n\n\n In the above screen, notice that the temp table name has some values at the end. It is the unique identifier and SQL server generates it automatically. This is because the multiple database connections can create the same temp table. To differentiate the temp tables created by different connections, the unique identifier is appended at the end of the table name.\u00a0 <\/p>\n\n\n\n The scope of the local temp table is available only for the connection that has created the table. You should perform the DDL\/ DML operations on the temp table in the same connection. When you close the connection that creates the temp table, it drops automatically. You can also drop the temp table manually by using DROP TABLE statement.<\/p>\n\n\n\n The global temporary table has two-pound signs (##) as the first character of the name.<\/p>\n\n\n\n In contrast to local temporary table, the scope of the global temp table is available to all the open connections. Since it can be accessed by all connections, no unique identifier is added at the end of the table name.<\/p>\n\n\n\n When you close the last connection that references the temp table, it drops automatically. You can also drop the temp table manually by using DROP TABLE statement. <\/p>\n\n\n\n Like permanent tables, temp tables can have constraints and indexes. <\/p>\n\n\n\nGlobal Temporary Table<\/strong> <\/h2>\n\n\n\n
SELECT \n playerID, \n FirstName \nINTO ##PlayerDetails \nFROM tblPlayer \nWHERE countryID = 1 --India <\/pre>\n\n\n\n
Difference between local and global temp table<\/strong><\/h2>\n\n\n\n