Temporary tables are like normal tables , but these are used to store the result temporarily.We can perform the same operations on the temporary tables as normal tables such as select,delete,update.Unlike normal tables temporary tables are automatically deleted when the user session ends or we manually destroy the table.These tables are stored in the tempdb database.
In SQL Server there are two types of temporary tables
Local Temporary tables
The name of local temporary table start with the pound(#) sign.These are automatically destroyed when the session or the connection which created the table is closed.
Global Temporary tables
The name of global temporary table start with the double pound (##) sign.Unlike local temporary tables once a global temporary table has been created it is destroyed only when the last connection is closed.
In the following example there are two tables defined in the database Users table and UserInfo table.UserInfo table contains the personal details about the user while the Users table contains the account information about the user.
If we require a temporary result set which contains data from both the tables then we can define a temporary table
CREATE TABLE #UserDetails (UserName varchar(50), FirstName varchar(50), LastName varchar(50), Email varchar(50))
For populating the temporary table with the data from both the tables we can use a INSERT INTO statement as
INSERT INTO #UserDetails SELECT us.UserName,usi.FIRSTNAME,usi.LASTNAME,us.Email FROM [dbo].[Users] us JOIN [dbo].[UserInfo] usi ON us.UserName=usi.UserName