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
19
votes
9 answers

SQL Insert Into Temp Table in both If and Else Blocks

I'm trying to populate a temp table based on the result of a condition in SQL 2005. The temp table will have the same structure either way, but will be populated using a different query depending on the condition. The simplified example script…
CuppM
  • 1,668
  • 4
  • 19
  • 30
19
votes
2 answers

Using temp table with exec @sql in stored procedure

I have a stored procedure and part of them as below: @DRange is a incoming varchar value declare @sql varchar(max) set @sql = 'select * into #tmpA from TableA where create_date >= getDate - ' + @DRange + '' and is_enabled = 1' exec (@sql) select *…
William Tang
  • 215
  • 1
  • 2
  • 8
18
votes
4 answers

How to find difference between two columns data?

I have a temp table with two columns of integer data i want to find the difference between two columns in 3rd column. #TEMP1 Present previous 59 88 75 75 45 45 77 88 09 08 #temp2 Difference 29 …
vision
  • 415
  • 2
  • 4
  • 12
17
votes
4 answers

Force Oracle Drop Global Temp Table

In our project I create some global temp table that will be like these: CREATE GLOBAL TEMPORARY TABLE v2dtemp ( id NUMBER, GOOD_TYPE_GROUP VARCHAR2(250 BYTE), GOOD_CODE VARCHAR2(50 BYTE), GOOD_TITLE …
Naeem Baghi
  • 811
  • 2
  • 14
  • 29
17
votes
5 answers

Where Do Temporary Tables Get stored in sql server?

Where do temporary tables get stored in a database? I want to drop a temporary table if it already exists. I can do this for securable tables by querying at information schema but I don't know where temporary tables are stored.
Shantanu Gupta
  • 20,688
  • 54
  • 182
  • 286
17
votes
3 answers

From a performance perspective, how efficient is it to use a MySQL temporary table for a highly used website feature?

I'm attempting to write a search functionality for a website, and I've decided upon an approach of using MySQL temporary tables to handle the data input, via the query below: CREATE TEMPORARY TABLE `patternmatch` (`pattern`…
marked-down
  • 9,958
  • 22
  • 87
  • 150
16
votes
5 answers

dropping a global temporary table

2 Separate questions. I am using this script to drop a table [SOLVED] BEGIN EXECUTE IMMEDIATE 'DROP TABLE_NAME'; DBMS_OUTPUT.PUT_LINE ('Global table TABLE_NAME Dropped'); EXCEPTION WHEN OTHERS THEN …
x.509
  • 2,205
  • 10
  • 44
  • 58
16
votes
3 answers

How can I limit the size of temporary tables?

I have largish (InnoDB) tables in a database; apparently the users are capable of making SELECTs with JOINs that result in temporary, large (and thus on-disk) tables. Sometimes, those are so large that they exhaust disk space, leading to all sorts…
Piskvor left the building
  • 91,498
  • 46
  • 177
  • 222
16
votes
2 answers

A solution to SQLAlchemy temporary table pain?

It seems like the biggest drawback with SQLAlchemy is that it takes several steps backwards when it comes to working with temporary tables. A very common use case, for example, is to create a temporary table that is very specific to one task, throw…
JamesHutchison
  • 885
  • 2
  • 8
  • 17
16
votes
6 answers

How to return temporary table from stored procedure

CREATE PROCEDURE [test].[proc] @ConfiguredContentId int, @NumberOfGames int AS BEGIN SET NOCOUNT ON RETURN @WunNumbers TABLE (WinNumb int) INSERT INTO @WunNumbers (WinNumb) SELECT TOP (@NumberOfGames) WinningNumber FROM [Game].[Game] g …
dani
  • 469
  • 3
  • 12
  • 23
15
votes
1 answer

How to bulk delete records using temporary tables in Hibernate?

I have a question. Where did these methods go? Dialect.supportsTemporaryTables(); Dialect.generateTemporaryTableName(); Dialect.dropTemporaryTableAfterUse(); Dialect.getDropTemporaryTableString(); I've tried to browse git history for Dialect.java,…
viliam
  • 503
  • 6
  • 23
15
votes
2 answers

Find the column names of Temp table

I need to find the column names of temp table. If it is a physical table then we can either use sys.columns or Information_schema.columns system views to find the column names. Similarly is there a way to find the column names present in temp…
Pரதீப்
  • 91,748
  • 19
  • 131
  • 172
15
votes
3 answers

Using dapper, why is a temp table created in one use of a connection not available in a second use of the same connection

I'm trying to perform a series of SQL*Server steps using dapper from C#. One step creates a temp table and populates it. Following steps query data from the temp table. The create/populate seems to run successfully, but the first query from the…
Bob Wolfson
  • 281
  • 2
  • 9
15
votes
1 answer

Scope of temp tables created in MySQL stored procedure

QUESTION WITHDRAWN! When I spell everything correctly, the problem goes away! I have a MySQL stored procedure which creates a temporary table. When I call the procedure from the mysql prompt, it appears to run successfully, but if I then SELECT…
user1956095
  • 151
  • 1
  • 1
  • 5
14
votes
3 answers

CTE (Common Table Expression) vs Temp tables or Table variables, which is faster?

CTE (Common Table Expression) vs Temp tables or Table variables, which is faster?
Numan
  • 3,918
  • 4
  • 27
  • 44
1 2 3
…
99 100