SQL – Creating and Dropping Temp Tables

I run into this all the time but can’t ever remember the syntax.

First, check to see if it exists and if it does, drop it.

IF OBJECT_ID('tempdb..#MyTempTable') IS NOT NULL
Drop Table #MyTempTable

Second, build your table.  The “#” is important.  The “#” denotes it as a temp table.  Forget the hash and you’ll be creating permanent tables in your database every time the code runs.

Create Table #MyTempTable
 ( MyID int,
   MyText varchar(50)
 )

When creating you’re table you can include any kind and amount of fields that you can in a regular table. This is just a demo.

At this point you can fill the temp table with records and do whatever queries against it that your code requires as long as they occur in the same context.  Once the code stops running the temp table ceases to be available.

Now, go create some temp tables!