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
14
votes
2 answers

Temporary Tables in Stored Procedures: To Drop or Not To Drop?

I have come across this question quite a few times but have been unable to find an answer that fully satisfies me. Basically, what most people and books say is that "Although temporary tables are deleted when they go out of scope, you should…
Dmitrij Kultasev
  • 5,447
  • 5
  • 44
  • 88
14
votes
1 answer

Unable to use Common Table Expressions in Postgres Crosstab Query

I'm trying to use perform a pivot operation on some data using Postgres' tablefunc extension's CROSSTAB function. The data needs to undergo some transformations first, which I perform in some common table expressions. However, it appears CROSSTAB…
bosticko
  • 763
  • 9
  • 21
14
votes
5 answers

There is already an object named '##Temp' in the database

I have a stored procedure on SQL Server 2000. It contains: select ... into ##Temp ... ... drop table ##Temp When I run the stored procedure with ADO a second time, it prompts: There is already an object named '##Temp' in the database. Could anyone…
phoenies
  • 537
  • 2
  • 5
  • 16
14
votes
2 answers

CTE and temporary index on PostgreSQL

I work on postgres database where I'll using a mix of relational tables and jsonb tables (which can be indexed). I have being using a lot of CTE queries to insert or update data selecting from a temporary table, like: WITH information as ( …
Matheus Hernandes
  • 629
  • 1
  • 6
  • 25
14
votes
2 answers

Difference between heap table and temporary table in MySQL?

I am very new to MySQL. My question may wrong, if it is please correct or explain it. I Just read about Heap table and temporary table by searching definition on Google. What is the exact difference between them and what real time use of both? As…
Prashant Shilimkar
  • 533
  • 1
  • 3
  • 10
14
votes
6 answers

SQL Server Creating a temp table for this query

I have this query: DECLARE @ProjectID int = 3, @Year int = 2010, @MeterTypeID int = 1, @StartDate datetime, @EndDate datetime SET @StartDate = '07/01/' + CAST(@Year as VARCHAR) SET @EndDate = '06/30/' + CAST(@Year+1 as VARCHAR) SELECT …
Natalia Natalie
  • 647
  • 3
  • 10
  • 17
14
votes
3 answers

Update from Temp Table

Query: SELECT ID, T.c.value('@Address', 'nvarchar(20)' ) as Address INTO #TMP FROM TABLE1 CROSS APPLY XMLData.nodes('/Document') AS T(c) UPDATE TABLE1 SET HomeAddress = (SELECT TOP 1 t.Address FROM #TMP t …
SF Developer
  • 5,244
  • 14
  • 60
  • 106
14
votes
2 answers

How to copy structure and contents of a table, but with separate sequence?

I'm trying to setup temporary tables for unit-testing purposes. So far I managed to create a temporary table which copies the structure of an existing table: CREATE TEMP TABLE t_mytable (LIKE mytable INCLUDING DEFAULTS); But this lacks the data…
Stefan Majewsky
  • 5,427
  • 2
  • 28
  • 51
13
votes
1 answer

Default collation of temporary tables

How can I check what the collation of a temporary table is? I want to do this because I want to check what happens if I have database with a specific collation, different from the SQL Server instance and create a temporary table. Let's say we have…
gotqn
  • 42,737
  • 46
  • 157
  • 243
13
votes
6 answers

Is it safe to put an index on an Oracle Temporary Table?

I have read that one should not analyze a temp table, as it screws up the table statistics for others. What about an index? If I put an index on the table for the duration of my program, can other programs using the table be affected by that…
EvilTeach
  • 28,120
  • 21
  • 85
  • 141
13
votes
3 answers

Best use of indices on temporary tables in T-SQL

If you're creating a temporary table within a stored procedure and want to add an index or two on it, to improve the performance of any additional statements made against it, what is the best approach? Sybase says this: "the table must contain data…
ninesided
  • 23,085
  • 14
  • 83
  • 107
13
votes
2 answers

Use temp table with SQLAlchemy

I am trying to use use a temp table with SQLAlchemy and join it against an existing table. This is what I have so far engine = db.get_engine(db.app, 'MY_DATABASE') df = pd.DataFrame({"id": [1, 2, 3], "value": [100, 200, 300], "date": [date.today(),…
Kris Harper
  • 5,672
  • 8
  • 51
  • 96
13
votes
3 answers

Why is MySQL 'insert into ... select ...' so much slower than a select alone?

I'm trying to store a query result in a temporary table for further processing. create temporary table tmpTest ( a FLOAT, b FLOAT, c FLOAT ) engine = memory; insert into tmpTest ( select a,b,c from someTable where ... ); But…
Ben
  • 4,486
  • 6
  • 33
  • 48
12
votes
2 answers

Recommend usage of temp table or table variable in Entity Framework 4. Update Performance Entity framework

I need to update a bit field in a table and set this field to true for a specific list of Ids in that table. The Ids are passed in from an external process. I guess in pure SQL the most efficient way would be to create a temp table and populate it…
Liam
  • 1,267
  • 2
  • 17
  • 30
12
votes
1 answer

delete temporary files in postgresql

I have a huge database of about 800GB. When I tried to run a query which groups certain variables and aggregates the result, it was stopping after running for a couple of hours. Postgres was throwing a message that disk space is full. After looking…
let_there_be_light
  • 837
  • 3
  • 9
  • 15
1 2 3
…
99 100