Why in the world will anyone use temp tables, especially if we have to run the same report over and over again? The simple answer? Because it is what it is -- temporary. Which means you don't have to store data, that.. well.. requires permanent storage!
I use temp tables when I want to create a "Reference" for another table, usually when I am creating exceptions, lookups, and analysis that I will re-use again somewhere in my query. Most of the time, you can get away with just using joins, but that's not our topic of discussion right now. That one is a totally different ball game.
There are different ways of setting up temp tables:
Table Variables
Example: Declare @temp table
Description: visible only to the connection that creates it and are deleted when the batch of the stored procedure ends'
Local Temp Table
Example: Create Table #temptable
Description: visible only to the connection that created it and are deleted when the connection is closed
Global Temp Table
Example: Create table ##temptable
Description: visible to everyone, and are deleted when all connections that have referenced them have closed
Tempdb Permanent Tables:
Example: Use tempdb create table temptbl
Description: are visible to everyone, and are deleted when the server is restarted
Below is an example of how to create a temp table you can copy-paste into your SSMS that you can test and play around with. You can simple change the statements to reflect different types of temp tables, if desired.
Code:
/*--------------------------------------------------------------------------------------------------------------------------------------------
Report Name: Sample temp tables
Created by: LearnwithGlezy.com
For: Microsoft SSMS
DROP TABLE IF EXISTS: you want to make sure any instance in your server referring to the temporary table is not present. If you have not used SQL before, this is the same as deleting the file so you can save it.
CREATE TABLE: this is the command to start creation of the table
Data Types: We use data type to identify what our column is going to be (text, numbers, dates, etc)
Different Data types: https://learn.microsoft.com/en-us/sql/t-sql/data-types/data-types-transact-sql?view=sql-server-ver16
INSERT INTO - VALUES: this is the command that will prompt to start inserting values into your table in a row by row setting
If you want to create a global temp table, just use ## in the beg
inning, otherwise a single # is used.
Without being too technical, # is just for you and ## is if you want others to be able to access and re-use your code.
Global temp table is not normally used in business setting, at least from where I've worked today and in the past.
Simply because anyone who have write access can modify and drop them, even as you are using it.
It's also a whole lot of mess if your naming convention happens to be the same as someone else
WARNING: You cannot create a temporary table, drop it, and then create a new temporary table with the same name.
--------------------------------------------------------------------------------------------------------------------------------------------*/
--------------------------------------------------------------------------------------------------------------------------------------------
--Populating the table, multiple insert command
--------------------------------------------------------------------------------------------------------------------------------------------
DROP TABLE IF EXISTS #Example1
CREATE TABLE #Example1 (OrderNum VARCHAR(10), DateRef DATE, Reason VARCHAR (MAX))
INSERT INTO #Example1 VALUES ('2233445566' ,'09/19/2022' ,'Timing, bill next month')
INSERT INTO #Example1 VALUES ('1122334455' ,'09/01/2022' ,'Timing, paid prior month')
SELECT * from #Example1
--------------------------------------------------------------------------------------------------------------------------------------------
--Populating the table, single insert command, this is the same as the first example, but we've eliminated the 'Insert into' command and replaced with comma
--------------------------------------------------------------------------------------------------------------------------------------------
DROP TABLE IF EXISTS #Example2
CREATE TABLE #Example2 (OrderNum VARCHAR(10), DateRef DATE, Reason VARCHAR (MAX))
INSERT INTO #Example2 VALUES
('2233445566' ,'09/19/2022' ,'Timing, bill next month'),
('1122334455' ,'09/01/2022' ,'Timing, paid prior month')
SELECT * from #Example2
--------------------------------------------------------------------------------------------------------------------------------------------
--Populating the table, using information from another table
--Here I am referring to #Example1 table, but you can refer to your table of choice for your testing
--------------------------------------------------------------------------------------------------------------------------------------------
DROP TABLE IF EXISTS #Example3
CREATE TABLE #Example3 (OrderNum VARCHAR(10), DateRef DATE, Reason VARCHAR (MAX))
SELECT * from #Example3
--------------------------------------------------------------------------------------------------------------------------------------------
--Direct population using already existing data type
--Here I am referring to #Example1 table, but you can refer to your
table of choice for your testing
--------------------------------------------------------------------------------------------------------------------------------------------
DROP TABLE IF EXISTS #Example4
SELECT OrderNum, DateRef, Reason
INTO #Example4
FROM #Example1
SELECT * from #Example4
Sample Result:
Edits:
10/17/2022: Added different ways of setting up temp tables section
Comments