How to Create a Temp Table in SQL Server (#Temp, SELECT INTO, ##Global)

Some reports take two passes: pull a slice of orders, then total, join or fix it before the final SELECT. A temp table holds that slice for you and cleans itself up.

Quick answer: to create a temp table in SQL Server, put # in front of the name: CREATE TABLE #TxOrders (OrderID int PRIMARY KEY, Total decimal(10,2));. Or build and fill it in one step with SELECT ... INTO #TxOrders FROM dbo.Orders;

In this tutorial, I’ll walk you through every SQL Server create temp table option we use, plus where the tables live in tempdb, how long they last, and the errors you’ll hit.

What You Need Before You Start

• SQL Server 2016 or later for DROP TABLE IF EXISTS; we ran everything on SQL Server 2025.
• SSMS 21, VS Code or sqlcmd. Any login that can connect can create temp tables, no extra permission.
• A sample table. We use dbo.Orders in a demo database called DfqTempTable.

Here’s the sample. Every example below reads from these eight orders:

CREATE TABLE dbo.Orders (
    OrderID   int           PRIMARY KEY,
    Customer  nvarchar(20)  NOT NULL,
    State     char(2)       NOT NULL,
    OrderDate date          NOT NULL,
    Total     decimal(10,2) NOT NULL,
    Status    nvarchar(10)  NOT NULL
);
INSERT INTO dbo.Orders (OrderID, Customer, State, OrderDate, Total, Status) VALUES
(1001, N'Megan Bowen',     'WA', '2026-09-02',  245.50, N'Shipped'),
(1002, N'Alex Wilber',     'TX', '2026-09-05',   89.99, N'Shipped'),
(1003, N'Adele Vance',     'CA', '2026-09-14', 1210.00, N'Shipped'),
(1004, N'Diego Siciliani', 'TX', '2026-09-21',  432.75, N'Pending'),
(1005, N'Lee Gu',          'NY', '2026-10-01',   67.20, N'Pending'),
(1006, N'Nestor Wilke',    'TX', '2026-10-08',  158.00, N'Pending'),
(1007, N'Lynne Robbins',   'WA', '2026-10-12',  990.40, N'Pending'),
(1008, N'Isaiah Langer',   'IL', '2026-10-19',  312.15, N'Pending');

Keep an eye on the three Texas orders; they show up in most steps. If you’re building the table by hand, here’s how to create a table in SQL Server Management Studio.

Step 1: Create a Temp Table With CREATE TABLE #Temp

A single # makes a local temp table that only your session can see. You define the columns like any table, then load it with INSERT INTO ... SELECT:

SET NOCOUNT ON;
CREATE TABLE #TxOrders (
    OrderID  int           PRIMARY KEY,
    Customer nvarchar(20)  NOT NULL,
    Total    decimal(10,2) NOT NULL
);

INSERT INTO #TxOrders (OrderID, Customer, Total)
SELECT OrderID, Customer, Total
FROM dbo.Orders
WHERE State = 'TX';

SELECT OrderID, Customer, Total FROM #TxOrders;

Output:

OrderID     Customer             Total       
----------- -------------------- ------------
       1002 Alex Wilber                 89.99
       1004 Diego Siciliani            432.75
       1006 Nestor Wilke               158.00

The temp table now holds orders 1002, 1004 and 1006, worth $680.74 together. The insert works the same as on a regular table, so our guide to INSERT INTO SELECT in SQL Server applies here too.

Note: a local temp table name can’t be longer than 116 characters, and any schema you type is ignored. All temp tables are created in dbo.

Add an IDENTITY column to a temp table

Same idea, now with a column that numbers itself. Leave it out of the INSERT and SQL Server fills it in:

SET NOCOUNT ON;
CREATE TABLE #FollowUps (
    FollowUpID int IDENTITY(1,1) PRIMARY KEY,
    OrderID    int          NOT NULL,
    Note       nvarchar(30) NOT NULL
);

INSERT INTO #FollowUps (OrderID, Note)
VALUES (1004, N'Call about delivery date'),
       (1007, N'Confirm Seattle address');

SELECT FollowUpID, OrderID, Note FROM #FollowUps;

Output:

FollowUpID  OrderID     Note                          
----------- ----------- ------------------------------
          1        1004 Call about delivery date      
          2        1007 Confirm Seattle address

The two follow-up notes got IDs 1 and 2. For seed and increment options, see our identity column in SQL Server guide.

Step 2: Create a Temp Table With SELECT INTO #Temp

SELECT ... INTO creates the temp table and fills it in one statement, taking column names and types from the query. Same orders, now totaled by state:

SET NOCOUNT ON;
SELECT State, COUNT(*) AS Orders, SUM(Total) AS Revenue
INTO #StateTotals
FROM dbo.Orders
GROUP BY State;

SELECT State, Orders, Revenue
FROM #StateTotals
ORDER BY Revenue DESC;

SELECT CAST(name AS varchar(10)) AS ColumnName,
       CAST(TYPE_NAME(user_type_id) AS char(8)) AS Type,
       precision, scale
FROM tempdb.sys.columns
WHERE object_id = OBJECT_ID('tempdb..#StateTotals');

Output:

State Orders      Revenue                                 
----- ----------- ----------------------------------------
WA              2                                  1235.90
CA              1                                  1210.00
TX              3                                   680.74
IL              1                                   312.15
NY              1                                    67.20
ColumnName Type     precision scale
---------- -------- --------- -----
State      char             0     0
Orders     int             10     0
Revenue    decimal         38     2

Washington leads with $1,235.90 over two orders. Look at the types: COUNT(*) became int and SUM(Total) became decimal(38,2), so SQL Server widened the precision for you.

Pro Tip: I use SELECT INTO for quick scratch work and CREATE TABLE when the types matter. To copy a real table permanently instead, see how to back up a table in SQL Server.

Step 3: Add a Primary Key and Indexes to a Temp Table

Temp tables take a primary key, inline indexes and CREATE INDEX, just like real tables. We also tried a FOREIGN KEY to see what happens:

SET NOCOUNT ON;
CREATE TABLE #OrderWork (
    OrderID int PRIMARY KEY REFERENCES dbo.Orders (OrderID),
    State   char(2)       NOT NULL,
    Total   decimal(10,2) NOT NULL,
    INDEX IX_State NONCLUSTERED (State)
);
CREATE NONCLUSTERED INDEX IX_Total ON #OrderWork (Total);

SELECT CAST(LEFT(name, 14) AS varchar(14)) AS IndexName,
       CAST(type_desc AS varchar(12)) AS IndexType
FROM tempdb.sys.indexes
WHERE object_id = OBJECT_ID('tempdb..#OrderWork');

Output:

Skipping FOREIGN KEY constraint '#OrderWork' definition for temporary table. FOREIGN KEY constraints are not enforced on local or global temporary tables.
IndexName      IndexType   
-------------- ------------
PK__#OrderWo__ CLUSTERED   
IX_State       NONCLUSTERED
IX_Total       NONCLUSTERED

The table got a clustered primary key with a system name (cut to 14 characters here) plus IX_State and IX_Total. The REFERENCES clause only printed a warning, because foreign keys aren’t enforced on temp tables.

Important: leave the primary key unnamed in procedures. A named constraint inside a transaction means only one session at a time can run the CREATE.

Step 4: Create a Global Temp Table (##Temp)

Two number signs make a global temp table that every session on the instance can read. Here we build ##StateRevenue, then create one local and one global table inside an EXEC:

SET NOCOUNT ON;
SELECT State, SUM(Total) AS Revenue
INTO ##StateRevenue
FROM dbo.Orders
GROUP BY State;

-- One local and one global table created inside EXEC
EXEC (N'CREATE TABLE #InnerLocal (id int);
        CREATE TABLE ##InnerGlobal (id int);');

SELECT CASE WHEN OBJECT_ID('tempdb..#InnerLocal') IS NULL
            THEN 'dropped' ELSE 'exists' END AS InnerLocal,
       CASE WHEN OBJECT_ID('tempdb..##InnerGlobal') IS NULL
            THEN 'dropped' ELSE 'exists' END AS InnerGlobal,
       (SELECT COUNT(*) FROM ##StateRevenue) AS GlobalRows;

Output:

InnerLocal InnerGlobal GlobalRows 
---------- ----------- -----------
dropped    exists                5

##StateRevenue holds five state rows. The local table created inside EXEC was already gone when EXEC finished, while the global one was still there.

A global table stays until the session that created it ends and no other statement is using it. Because any session can change it, we only use one for short hand-offs.

How Long Does a Temp Table Last? Scope and Lifetime

SQL Server drops a temp table automatically when it goes out of scope. One made inside a stored procedure disappears when the procedure finishes:

CREATE OR ALTER PROCEDURE dbo.usp_TxSummary
AS
    SET NOCOUNT ON;
    SELECT OrderID, Total INTO #ProcOrders
    FROM dbo.Orders WHERE State = 'TX';
    SELECT COUNT(*) AS TxOrders, SUM(Total) AS TxRevenue
    FROM #ProcOrders;
GO

EXEC dbo.usp_TxSummary;

-- The procedure has finished: is #ProcOrders still there?
BEGIN TRY EXEC (N'SELECT * FROM #ProcOrders'); END TRY
BEGIN CATCH PRINT CONCAT(ERROR_NUMBER(), ': ', ERROR_MESSAGE());
END CATCH;

Output:

TxOrders    TxRevenue                               
----------- ----------------------------------------
          3                                   680.74
208: Invalid object name '#ProcOrders'.

The procedure saw 3 Texas orders worth $680.74. One line later, #ProcOrders was gone and we got error 208. Nested procedures it calls can still read it, which temp tables in a stored procedure covers in depth.

Outside a procedure, a local temp table lasts until your session ends. I ran this as a fresh sqlcmd session after Step 1:

-- A new sqlcmd session: #TxOrders from create-temp-table.sql is gone
BEGIN TRY EXEC (N'SELECT * FROM #TxOrders'); END TRY
BEGIN CATCH PRINT CONCAT(ERROR_NUMBER(), ': ', ERROR_MESSAGE());
END CATCH;

Output:

208: Invalid object name '#TxOrders'.

Note: I wrap the SELECT in EXEC() on purpose. TRY…CATCH can’t catch a missing-object error at its own level, but it does catch one from the lower level that EXEC runs in.

Step 5: Drop a Temp Table With DROP TABLE IF EXISTS #Temp

You don’t have to drop temp tables, but rerunning a script in the same SSMS window will fail if the old one is still there. Start the script with DROP TABLE IF EXISTS:

SET NOCOUNT ON;
DROP TABLE IF EXISTS #TxOrders;          -- SQL Server 2016+

SELECT OrderID, Total INTO #TxOrders
FROM dbo.Orders WHERE State = 'TX';

PRINT CONCAT('Loaded #TxOrders, rows: ', @@ROWCOUNT);
GO 2

Output:

Loaded #TxOrders, rows: 3
Loaded #TxOrders, rows: 3

GO 2 runs the batch twice in one session, and both runs loaded 3 rows without an error. Our SQL Server DROP TABLE IF EXISTS guide covers permanent tables too.

On versions older than 2016, check OBJECT_ID('tempdb..#name') first. The two dots mean “default schema in tempdb”:

SET NOCOUNT ON;
IF OBJECT_ID('tempdb..#TxOrders') IS NOT NULL
    DROP TABLE #TxOrders;                -- works on any version
CREATE TABLE #TxOrders (OrderID int);
PRINT 'Created #TxOrders';
GO 2

Output:

Created #TxOrders
Created #TxOrders

Where Are Temp Tables Stored in SQL Server? (tempdb)

Every temp table lives in the tempdb system database, whatever database you’re connected to. This query finds both kinds in tempdb.sys.tables:

SET NOCOUNT ON;
CREATE TABLE #TxOrders (OrderID int);
CREATE TABLE ##StateRevenue (State char(2));

SELECT CAST(LEFT(name, 22) AS varchar(22)) AS NameInTempdb,
       LEN(name) AS NameLength
FROM tempdb.sys.tables
WHERE object_id = OBJECT_ID('tempdb..#TxOrders')
   OR name = '##StateRevenue'
ORDER BY NameLength DESC;

SELECT CASE WHEN OBJECT_ID('tempdb..#TxOrders') IS NOT NULL
            THEN 'exists' ELSE 'missing' END AS TxOrdersCheck;

Output:

NameInTempdb           NameLength 
---------------------- -----------
#TxOrders_____________         128
##StateRevenue                  14
TxOrdersCheck
-------------
exists

The local table is stored under a 128-character name: #TxOrders, underscores, then a 12-character suffix. That suffix lets many sessions each own a #TxOrders, while ##StateRevenue stays as typed.

That’s why WHERE name = '#TxOrders' finds nothing, so use OBJECT_ID('tempdb..#TxOrders') or LIKE '#TxOrders[_]%'. Querying tempdb.sys.objects with name LIKE '#%' works too.

How to see temp tables in SSMS Object Explorer

1. In Object Explorer, expand your server, then Databases.
2. Expand System Databases > tempdb.
3. Expand Temporary Tables. You’ll see local tables with their long padded names.
4. Created a table after opening the folder? Right-click Temporary Tables and select Refresh.

You can’t open another session’s local table from there. For permanent tables, here’s how to view a table in SQL Server.

Temp Table vs Table Variable: @t vs #temp

A table variable (DECLARE @t TABLE) looks similar but behaves differently. The clearest test is a ROLLBACK, on the same two Texas order IDs:

SET NOCOUNT ON;
CREATE TABLE #Tx (OrderID int);
DECLARE @Tx TABLE (OrderID int);

BEGIN TRANSACTION;
    INSERT INTO #Tx VALUES (1002), (1004);
    INSERT INTO @Tx VALUES (1002), (1004);
ROLLBACK;

SELECT (SELECT COUNT(*) FROM #Tx) AS TempTableRows,
       (SELECT COUNT(*) FROM @Tx) AS TableVariableRows;

Output:

TempTableRows TableVariableRows
------------- -----------------
            0                 2

The rollback emptied the temp table, but the table variable kept both rows. Here’s how the two compare:

Feature	#temp table	@table variable
Create with	CREATE TABLE or SELECT INTO	DECLARE @t TABLE (…)
Scope	Session or procedure	Batch, procedure or function
Stored in	tempdb	tempdb
SELECT INTO target	Yes	No
Indexes	Any time with CREATE INDEX	Inline in DECLARE only
Statistics	Yes	No
ROLLBACK	Undoes the inserts	Keeps the rows
Inside a function	No (error 2772)	Yes
Best for	Larger sets you join or reuse	Small sets, about 100 rows or fewer

Microsoft’s own advice is to test both. We default to a temp table once the rows climb into the hundreds or the query joins on it. A table variable is also how a SQL Server function returns a table.

When Should You Use a CTE Instead of a Temp Table?

If you only need the intermediate rows once, in one statement, a CTE is simpler. Nothing gets created in tempdb:

SET NOCOUNT ON;
WITH StateTotals AS (
    SELECT State, SUM(Total) AS Revenue
    FROM dbo.Orders
    GROUP BY State
)
SELECT State, Revenue
FROM StateTotals
WHERE Revenue > 1000;

Output:

State Revenue                                 
----- ----------------------------------------
CA                                     1210.00
WA                                     1235.90

Washington ($1,235.90) and California ($1,210.00) pass the $1,000 filter. Pick a temp table when several statements reuse the rows, you need an index, or you’ll join it to other tables more than once.

Temp Table Cheat Sheet

Task	T-SQL
Create a local temp table	CREATE TABLE #t (id int PRIMARY KEY);
Create and fill from a query	SELECT ... INTO #t FROM ...;
Fill an existing temp table	INSERT INTO #t SELECT ...;
Create a global temp table	CREATE TABLE ##t (...);
Add an index	CREATE INDEX IX_c ON #t (c);
Drop if it exists (2016+)	DROP TABLE IF EXISTS #t;
Check that it exists	OBJECT_ID('tempdb..#t')
List temp tables	SELECT name FROM tempdb.sys.tables;

Common Mistakes to Avoid With Temp Tables

These are the errors we reproduced on SQL Server 2025, each caught with TRY…CATCH so the number prints:

SET NOCOUNT ON;
CREATE TABLE #TxOrders (OrderID int);
GO
-- 1. Same name again in the same session
BEGIN TRY CREATE TABLE #TxOrders (OrderID int); END TRY
BEGIN CATCH PRINT CONCAT(ERROR_NUMBER(), ': ', ERROR_MESSAGE());
END CATCH;

-- 2. A temp table inside a function
BEGIN TRY
    EXEC (N'CREATE FUNCTION dbo.fn_TxCount() RETURNS int AS
            BEGIN CREATE TABLE #t (id int); RETURN 1; END');
END TRY
BEGIN CATCH PRINT CONCAT(ERROR_NUMBER(), ': ', ERROR_MESSAGE());
END CATCH;

Output:

2714: There is already an object named '#TxOrders' in the database.
2772: Cannot access temporary tables from within a function.

Error 2714 means the table from your last run is still there, so add DROP TABLE IF EXISTS at the top. Error 2772 means functions can’t touch temp tables; use a table variable or a stored procedure instead.

Views can’t use temp tables either. We tried it on the same session’s #TxOrders:

SET NOCOUNT ON;
CREATE TABLE #TxOrders (OrderID int);
GO
BEGIN TRY EXEC (N'CREATE VIEW dbo.vTxOrders AS SELECT OrderID FROM #TxOrders'); END TRY
BEGIN CATCH PRINT CONCAT(ERROR_NUMBER(), ': ', ERROR_MESSAGE());
END CATCH;

Output:

4508: Views or functions are not allowed on temporary tables. Table names that begin with '#' denote temporary tables.

The other two traps: Invalid object name '#ProcOrders' after the procedure ends, and creating one #temp twice in the same batch. That fails with error 2714 before anything runs, even with a DROP between them.

Next Steps With SQL Server Temp Tables

Once you’re comfortable with temp tables, the next step is filling one straight from a procedure’s result set with INSERT INTO … EXEC. Our guide on how to select from a stored procedure walks through it.

We’d point you to these next if temp tables are part of your daily T-SQL:

• Stored procedure vs function in SQL Server
• Loop through rows in a stored procedure
• Get a table’s row count in SQL Server
• Add a column to a SQL Server table

Frequently Asked Questions