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.

1496 questions
65
votes
5 answers

Is there a way to get a list of all current temporary tables in SQL Server?

I realize that temporary tables are session/connection bound and not visible or accessible out of the session/connection. I have a long running stored procedure that creates temporary tables at various stages. Is there a way I can see the list of…
AAsk
  • 1,335
  • 4
  • 16
  • 23
64
votes
15 answers

EF4 - The selected stored procedure returns no columns

I have query in a stored procedure that calls some linked servers with some dynamic SQL. I understand that EF doesn't like that, so I specifically listed all the columns that would be returned. Yet, it still doesn't like that. What am I doing wrong…
cjbarth
  • 3,512
  • 5
  • 37
  • 55
61
votes
5 answers

Temporary table in SQL server causing ' There is already an object named' error

I have the following issue in SQL Server, I have some code that looks like this: DROP TABLE #TMPGUARDIAN CREATE TABLE #TMPGUARDIAN( LAST_NAME NVARCHAR(30), FRST_NAME NVARCHAR(30)) SELECT LAST_NAME,FRST_NAME INTO #TMPGUARDIAN FROM…
Art F
  • 3,572
  • 10
  • 45
  • 74
55
votes
2 answers

Creating temporary tables in SQL

I am trying to create a temporary table that selects only the data for a certain register_type. I wrote this query but it does not work: $ CREATE TABLE temp1 (Select egauge.dataid, egauge.register_type, egauge.timestamp_localtime, …
user1970850
  • 621
  • 2
  • 6
  • 6
48
votes
9 answers

Are temporary tables thread-safe?

I'm using SQL Server 2000, and many of the stored procedures it use temp tables extensively. The database has a lot of traffic, and I'm concerned about the thread-safety of creating and dropping temp tables. Lets say I have a stored procedure which…
Juliet
  • 76,873
  • 44
  • 191
  • 224
47
votes
5 answers

View or Temporary Table - which to use in MS SQL Server?

I have a problem to decide whether to use a view or a temp table. I have a stored procedure that i call from program. In that SP i store the result of a long query in a temp table, name the columns and make another queries on that table store the…
ruedi
  • 4,417
  • 11
  • 41
  • 73
45
votes
2 answers

Why is there a HUGE performance difference between temp table and subselect

This is a question about SQL Server 2008 R2 I'm not a DBA, by far. I'm a java developer, who has to write SQL from time to time. (mostly embedded in code). I want to know if I did something wrong here, and if so, what I can do to avoid it to happen…
Ward
  • 2,532
  • 1
  • 18
  • 37
41
votes
9 answers

Execute sp_executeSql for select...into #table but Can't Select out Temp Table Data

Was trying to select...into a temp Table #TempTable in sp_Executedsql. Not its successfully inserted or not but there Messages there written (359 row(s) affected) that mean successful inserted? Script below DECLARE @Sql NVARCHAR(MAX); SET @Sql =…
Worgon
  • 1,407
  • 4
  • 19
  • 27
41
votes
3 answers

TSQL Define Temp Table (or table variable) Without Defining Schema?

Is there a way to define a temp table without defining it's schema up front?
Jeff
  • 7,466
  • 32
  • 92
  • 150
40
votes
4 answers

EF can't infer return schema from Stored Procedure selecting from a #temp table

Suppose the following: CREATE PROCEDURE [MySPROC] AS BEGIN CREATE TABLE #tempSubset( [MyPrimaryKey] [bigint] NOT NULL, [OtherColumn] [int] NOT NULL) INSERT INTO #tempSubset (MyPrimaryKey, OtherColumn) SELECT SomePrimaryKey,…
JoeBrockhaus
  • 2,593
  • 2
  • 37
  • 61
39
votes
2 answers

PostgreSQL equivalent of MySQL memory tables?

Does PostgreSQL have an equivalent of MySQL memory tables? These MySQL memory tables can persist across sessions (i.e., different from temporary tables which drop at the end of the session). I haven't been able to find anything with PostgreSQL that…
Elliot B.
  • 14,589
  • 9
  • 70
  • 99
35
votes
2 answers

Is MySQL Temporary table a shared resource?

I have a MySQL stored procedure that uses a temporary table. Assume that my table name is 'temp' and I use it to store some middle data. It will create at the beginning of procedure, and will drop at the end. CREATE PROCEDURE p() BEGIN CREATE…
Ehsan Khodarahmi
  • 4,390
  • 9
  • 57
  • 81
29
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,660
  • 13
  • 49
  • 58
28
votes
3 answers

How can I make a SQL temp table with primary key and auto-incrementing field?

What am I missing here? I'm trying to get the ID field to be the primary key and auto increment so that I don't need to insert it explicitly. CREATE TABLE #tmp ( ID INT IDENTITY(1, 1) , AssignedTo NVARCHAR(100), AltBusinessSeverity…
kacalapy
  • 8,667
  • 18
  • 69
  • 111
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
  • 58,921
  • 13
  • 103
  • 170
1
2
3
…
99 100