Stack Overflow
Stack Overflow

Questions tagged [temp-tables]

Temporary tables are a feature of RDBMS's as a means of storing intermediate results. Some RDBMS's make the distinction between local and global temporary tables. Temporary tables are typically dropped when the session ends for local, or when the temporary table is no longer referenced for global. Note that a temporary table is not the same as a table variable even though both are temporary in nature.

The term temporary table is somewhat misleading, because the tables are permanent, just the data is somewhat volatile.

Temporary tables are useful to store intermediate data. Since it only exists for a session or a transaction the RDBMS doesn't have to worry about locks in order to synchronize between different transactions or redo information. This makes temporary tables often faster and more efficient then normal tables.

1637 questions
30
votes
6 answers

What is the difference between a temporary table vs global temporary table in Oracle?

I have heard these two terms "temporary table" and "global temporary table" used pretty much in similar context. What is the difference between the two?
MOZILLA
  • 5,862
  • 14
  • 53
  • 59
29
votes
9 answers

Creating a Primary Key on a temp table - When?

I have a stored procedure that is working with a large amount of data. I have that data being inserted in to a temp table. The overall flow of events is something like CREATE #TempTable ( Col1 NUMERIC(18,0) NOT NULL, --This will not be an…
Frank V
  • 25,141
  • 34
  • 106
  • 144
28
votes
8 answers

SQL Server SELECT INTO and Blocking With Temp Tables

So, recently a DBA is trying to tell us that we cannot use the syntax of SELECT X, Y, Z INTO #MyTable FROM YourTable To create temporary tables in our environment, because that syntax causes a lock on TempDB for the duration of the stored procedure…
Mitchel Sellers
  • 62,228
  • 14
  • 110
  • 173
27
votes
5 answers

T-SQL Dynamic SQL and Temp Tables

It looks like #temptables created using dynamic SQL via the EXECUTE string method have a different scope and can't be referenced by "fixed" SQLs in the same stored procedure. However, I can reference a temp table created by a dynamic SQL statement…
Chad
  • 23,658
  • 51
  • 191
  • 321
27
votes
2 answers

What are the Main difference between CTE's and TEMP tables?

Is there a benefit to using CTE's (common table expressions) instead of using temp tables. I went through performance testing between both of them, but I cant find much difference between them. What are some pros and cons of using CTE'S?
GreatLakes07
  • 337
  • 1
  • 5
  • 11
26
votes
3 answers

SQL server temporary tables vs cursors

In SQL Server stored procedures when to use temporary tables and when to use cursors. which is the best option performance wise?
SARAVAN
  • 14,571
  • 16
  • 49
  • 70
25
votes
3 answers

T-SQL: Where xxx IN temporary table

I have a temp table and want to check in a where clause wether a certain id/string is contained in the temp table. Select... WHERE MyId IN MyTempTable I get a general error in MS SQL Management studio. is the "In" operator not suited for temp…
Elisabeth
  • 20,496
  • 52
  • 200
  • 321
25
votes
2 answers

ways to avoid global temp tables in oracle

We just converted our sql server stored procedures to oracle procedures. Sql Server SP's were highly dependent on session tables (INSERT INTO #table1...) these tables got converted as global temporary tables in oracle. We ended up with aroun 500…
Omnipresent
  • 29,434
  • 47
  • 142
  • 186
24
votes
3 answers

How to see temp table created by code in sql server?

I create a global temp table (i.e ##TheTable) using C# code. I want to be able to see that temp table in SQL server management studio after the code runs completely. Is it possible to do this ? If yes, then how ?
InTheSkies
  • 999
  • 2
  • 7
  • 13
24
votes
3 answers

SQL Server shows "Invalid object name '#temp'" when working with a temporary table

I have created a procedure create procedure testProcedure_One as DECLARE @Query nvarchar(4000) begin SET @Query = 'SELECT * into #temptest FROM Table1' Exec sp_Executesql @query SELECT * FROM #temptest drop table #temptest end When I run the…
Jebli
  • 2,775
  • 6
  • 29
  • 37
23
votes
2 answers

Is it possible to have temp tables in a function?

Apparently, I can't use them. I'm getting an error message like: Invalid use of a side-effecting operator 'SELECT' within a function If I want to do something like this: select bleh into #temp from Blah ... inside a function.
aarona
  • 35,986
  • 41
  • 138
  • 186
23
votes
4 answers

Deleting Global Temporary Tables (##tempTable) in SQL Server

Does SQL server automatically purge these out after a given length of inactivity or do I need to worry about purging them automatically? If so, how do I query for a list of tables to purge?
Jeff
  • 8,020
  • 34
  • 99
  • 157
22
votes
6 answers

TSQL select into Temp table from dynamic sql

This seems relatively simple, but apparently it's not. I need to create a temp table based on an existing table via the select into syntax: SELECT * INTO #TEMPTABLE FROM EXISTING_TABLE The problem is, the existing table name is accepted via a…
Patrick
  • 7,512
  • 7
  • 39
  • 50
22
votes
2 answers

Use a temp table in a Table-valued Functions

I'm trying to use a temp table in a table-valued function, but it seems I can not. Is there any other way I can use a temp table in a table-valued function? CURRENT CODE: CREATE FUNCTION dbo.fnt_AllChildren (@ParentName VARCHAR(255)) RETURNS…
Nick O
  • 3,716
  • 6
  • 38
  • 50
21
votes
3 answers

Are indexes on temporary tables deleted when the table is deleted?

Would the following SQL remove also the index - or does it have to be removed separately? CREATE TABLE #Tbl (field int) CREATE NONCLUSTERED INDEX idx ON #Tbl (field) DROP TABLE #Tbl
Manu
  • 28,753
  • 28
  • 75
  • 83
1 2
3
…
99 100