Stack Overflow
Stack Overflow

Questions tagged [global-temp-tables]

Global temporary tables are temporary tables visible to all sessions. The contents (and even the table depending on the DBMS) are automatically removed when the connection is closed.

Global temporary tables are visible to all sessions.

Syntax

For SQL Server

prefix of global temporary table names with a double number sign:

##table_name

For Firebird, Oracle and PostgreSQL

create global temporary table table_name (....);

Lifetime

For SQL Server

Global temporary tables are automatically dropped when the session that created the table ends and all other tasks have stopped referencing them. The association between a task and a table is maintained only for the life of a single Transact-SQL statement. This means that a global temporary table is dropped at the completion of the last Transact-SQL statement that was actively referencing the table when the creating session ended.

Official documentation

For Firebird, Oracle and PostgreSQL

Global temporary tables are created once and will exist until they are dropped using a DROP TABLE. The data in the GTT however is automatically removed when the session disconnects or when the transaction is committed.

74 questions
182
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,944
  • 9
  • 31
  • 42
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
6
votes
2 answers

MS SQL Server - safe concurrent use of global temp table?

In MS SQL Server, I'm using a global temp table to store session related information passed by the client and then I use that information inside triggers. Since the same global temp table can be used in different sessions and it may or may not exist…
Ioan H
  • 111
  • 9
6
votes
2 answers

Temp table with ### (triple) or more hashes

We know that in SQL Server, creating a table with # means "local temp table" and ## means "global temp table". But when I create a table like below: create table ###MyTable(IntColumn int, ValueColumn varchar(100)) Is this table a local or global…
Zerotoinfinity
  • 6,290
  • 32
  • 130
  • 206
4
votes
2 answers

mySQL Temporary Table is Full

I am trying to create and load a temporary mySQL table into memory using the following syntax but am running into a "Table is full" error: CREATE TEMPORARY TABLE IF NOT EXISTS tmpHistory ENGINE=MEMORY SELECT * FROM history ORDER BY date ASC; My…
Gunnar
  • 661
  • 1
  • 11
  • 29
4
votes
2 answers

"ORA-14450: attempt to access a transactional temp table already in use" in a compound trigger

I have a table which can hold many records for one account: different amounts. ACCOUNTID | AMOUNT id1 | 1 id1 | 2 id2 | 3 id2 | 4 Every time a record in this table is inserted/updated/deleted we need to evaluate an overall…
marius_work
  • 41
  • 1
  • 3
4
votes
2 answers

INSERT INTO temporary table from sp_executsql

Generally, I am bulding dynamic SQL statement that is executing using sp_executsql like this: EXEC sp_executesql @TempSQLStatement I need to insert the return result row set in something (table variable or temporary table), but I am getting the…
gotqn
  • 42,737
  • 46
  • 157
  • 243
4
votes
3 answers

MS SQL Temporary table

I was wondering, what is the difference between these two scripts? SELECT * FROM ##TEMP and this SELECT * FROM #TEMP
illumi
  • 458
  • 1
  • 14
  • 32
3
votes
2 answers

SQL GAPS AND ISLANDS

When there is a gap between commitments of the same type, the fact should contain multiple records that show the accurate start and end date of each continuous commitment. An example of this is patid 1001 and when there is a continuation of the…
Drdre01
  • 39
  • 5
3
votes
1 answer

Best practice for using SQL Server Temp Tables

I have a dashboard web application that involves users logging in to query large amounts of data (MS SQL Server 2016) and view charts of the results. The data also changes frequently (say every hour). Performance is particularly important. To…
rwatson89
  • 61
  • 4
3
votes
1 answer

Difference between createTempview and createGlobaltempview and CreateorReplaceTempview in spark 2.1?

What is the difference between createTempview and createGlobaltempview and CreateorReplaceTempview in spark 2.1 ??
Manikandan Balasubramanian
  • 1,079
  • 4
  • 14
  • 27
3
votes
1 answer

Firebird global temporary table (GTT), touch other tables?

I have a Firebird database (v. 2.5), I'm not allowed to create procedures, views or tables into the database, because of losing support. My view is too long: Too many Contexts of Relation/Procedure/Views. Maximum allowed is 255 I think I can solve…
Sam Enbank
  • 41
  • 1
  • 10
3
votes
1 answer

Sum Over SQL without grouping or ordering

I have a SQL Server query like this: CREATE TABLE ##Temp( OrderID NVARCHAR(100), ID INT, Prices INT, Total INT ); INSERT INTO ##Temp (ID, Prices, OrderID, Total) SELECT fc.ID, f.Prices, f.OrderID, (SUM(f.Prices) OVER())…
Mathematics
  • 7,314
  • 25
  • 77
  • 152
2
votes
3 answers

Sql Server : index already existing on global temp table

In Sql Server 2012 SP3 v.11.0.6020.0 (X64), I have a stored procedure which tests for the existence of a global temporary table (##MyTable , e.g.) and creates it - if not found, of course. IF OBJECT_ID ( 'tempdb..##MyTable' ) IS NULL CREATE…
M.Turrini
  • 738
  • 1
  • 4
  • 19
2
votes
0 answers

LAST_DDL_TIME for GLOBAL TEMPORARY TABLE -- Oracle 11g bug?

When I create a GLOBAL TEMPORARY TABLE in Oracle 11g (11.2.0.1.0), and then add a new column to it, Oracle does not update the LAST_DDL_TIME column of the system catalog views (e.g. DBA_OBJECTS): create global temporary table test# (foo…
Dan Lenski
  • 76,929
  • 13
  • 76
  • 124
1
2 3 4 5