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
700
votes
16 answers

Check if a temporary table exists and delete if it exists before creating a temporary table

I am using the following code to check if the temporary table exists and drop the table if it exists before creating again. It works fine as long as I don't change the columns. If I add a column later, it will give an error saying "invalid column".…
Sridhar
  • 8,016
  • 4
  • 23
  • 36
519
votes
7 answers

Create a temporary table in a SELECT statement without a separate CREATE TABLE

Is it possible to create a temporary (session only) table from a select statement without using a create table statement and specifying each column type? I know derived tables are capable of this, but those are super-temporary (statement-only) and I…
700 Software
  • 77,509
  • 74
  • 213
  • 324
399
votes
12 answers

What's the difference between a temp table and table variable in SQL Server?

In SQL Server 2005, we can create temp tables one of two ways: declare @tmp table (Col1 int, Col2 int); or create table #tmp (Col1 int, Col2 int); What are the differences between these two? I have read conflicting opinions on whether @tmp still…
Eric Z Beard
  • 35,488
  • 25
  • 97
  • 144
308
votes
6 answers

When should I use a table variable vs temporary table in sql server?

I'm learning more details in table variable. It says that temp tables are always on disk, and table variables are in memory, that is to say, the performance of table variable is better than temp table because table variable uses less IO operations…
yman
  • 3,145
  • 3
  • 12
  • 9
214
votes
14 answers

Inserting data into a temporary table

After having created a temporary table and declaring the data types like so; CREATE TABLE #TempTable( ID int, Date datetime, Name char(20)) How do I then insert the relevant data which is already held on a physical table within the database?
William
  • 5,412
  • 8
  • 34
  • 55
163
votes
7 answers

Local and global temporary tables in SQL Server

What is the difference between local and global temporary tables in SQL Server?
andrew Sullivan
  • 3,714
  • 8
  • 29
  • 41
160
votes
11 answers

SQL Server: Is it possible to insert into two tables at the same time?

My database contains three tables called Object_Table, Data_Table and Link_Table. The link table just contains two columns, the identity of an object record and an identity of a data record. I want to copy the data from DATA_TABLE where it is linked…
tpower
  • 53,004
  • 18
  • 65
  • 99
151
votes
12 answers

Which are more performant, CTE or temporary tables?

Which are more performant, CTE or Temporary Tables?
Blankman
  • 236,778
  • 296
  • 715
  • 1,125
99
votes
4 answers

How do you create a temporary table in an Oracle database?

I would like to create a temporary table in a Oracle database something like Declare table @table (int id) In SQL server And then populate it with a select statement Is it possible? Thanks
GigaPr
  • 4,788
  • 14
  • 55
  • 76
99
votes
24 answers

How can I simulate an array variable in MySQL?

It appears that MySQL doesn't have array variables. What should I use instead? There seem to be two alternatives suggested: A set-type scalar and temporary tables. The question I linked to suggests the former. But is it good practice to use these…
einpoklum
  • 86,754
  • 39
  • 223
  • 453
97
votes
8 answers

Getting around MySQL "Can't reopen table" error

I'm currently busy implementing a filter of sorts for which I need to generate an INNER JOIN clausse for every "tag" to filter on. The problem is that after a whole bunch of SQL, I have a table that contains all the information I need to make my…
Kris
  • 36,072
  • 8
  • 69
  • 94
88
votes
3 answers

Create a temporary table in MySQL with an index from a select

I have a stored function where I use temporary tables. For performance reasons, I need an index in that table. Unfortunately, I cannot use ALTER TABLE because this causes an implicit commit. Therefore I'm looking for the syntax to add the INDEX for…
solick
  • 1,873
  • 2
  • 14
  • 25
81
votes
2 answers

PostgreSQL temporary tables

I need to perform a query 2.5 million times. This query generates some rows which I need to AVG(column) and then use this AVG to filter the table from all values below average. I then need to INSERT these filtered results into a table. The only way…
Nicholas Leonard
  • 2,396
  • 3
  • 26
  • 31
73
votes
7 answers

How to retrieve field names from temporary table (SQL Server 2008)

I'm using SQL Server 2008. Say I create a temporary table like this one: create table #MyTempTable (col1 int,col2 varchar(10)) How can I retrieve the list of fields dynamically? I would like to see something like this: Fields: col1 col2 I was…
Anthony
  • 6,880
  • 12
  • 57
  • 70
66
votes
7 answers

Can you define "literal" tables in SQL?

Is there any SQL subquery syntax that lets you define, literally, a temporary table? For example, something like SELECT MAX(count) AS max, COUNT(*) AS count FROM ( (1 AS id, 7 AS count), (2, 6), (3, 13), (4, 12), (5, 9) )…
thomasrutter
  • 104,920
  • 24
  • 137
  • 160
1
2 3
…
99 100