Create Temporary Table in SQL Server

Updated on September 1, 2015     Kisan Patel

This tutorial will show you how to temporarily hold data into temporary table in SQL Server?

Temporary table is similar to table variable however, temporary table is not created in memory but it gets created physically in the database and it remains unless the current session ends or it is dropped explicitly.

  CREATE TABLE #myTempData
  (
     DetailId int,
     FullName varchar(50)
  )
  INSERT INTO #myTempData
  SELECT EmployeeID, FirstName + ' ' + LastName
  FROM [NORTHWND].[dbo].[Employees]
  SELECT * FROM #myTempData
 
 -- DROP this temp table now
   DROP table #myTempData

Temporary table name is prefixed with # character.

In above case, we have create a table named #myTempData with DetailId and FullName fields and inserting record from Employees table then selecting its records and when we are intended to not use this temp table any more, we have dropped it.