# Use of TEMP Table
# Dropping temp tables
Temp tables must have unique IDs (within the session, for local temp tables, or within the server, for global temp tables). Trying to create a table using a name that already exists will return the following error:
There is already an object named '#tempTable' in the database.
If your query produces temp tables, and you want to run it more than once, you will need to drop the tables before trying to generate them again. The basic syntax for this is:
drop table #tempTable
Trying to execute this syntax before the table exists (e.g. on the first run of your syntax) will cause another error:
Cannot drop the table '#tempTable', because it does not exist or you do not have permission.
To avoid this, you can check to see if the table already exists before dropping it, like so:
IF OBJECT_ID ('tempdb..#tempTable', 'U') is not null DROP TABLE #tempTable
# Local Temp Table
CREATE TABLE #LocalTempTable( StudentID int, StudentName varchar(50), StudentAddress varchar(150))
insert into #LocalTempTable values ( 1, 'Ram','India'); select * from #LocalTempTable
After executing all these statements if we close the query window and open it again and try inserting and select it will show an error message
“Invalid object name #LocalTempTable”
# Global Temp Table
CREATE TABLE ##NewGlobalTempTable( StudentID int, StudentName varchar(50), StudentAddress varchar(150)) Insert Into ##NewGlobalTempTable values ( 1,'Ram','India'); Select * from ##NewGlobalTempTable
Note: These are viewable by all users of the database, irrespective of permissions level.
Temporary Tables are really very helpful.
The table can be created at runtime and can do all operations which are done in a normal table.
These tables are created in a tempdb database.
Used when ?
Thus increases the performance.