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
0
votes
0 answers

Is there any way so that I can maintain records in a stored procedure and fetch them again in some other stored procedure

I need to maintain some values from one stored procedure and use them again in some other stored procedure. Below is the part of my stored procedure where I am creating a global temp table. IF object_id('tempdb..##TempNewSchedule') IS NULL BEGIN …
bhupesh
  • 104
  • 9
0
votes
1 answer

Stored Procedure Using Global Temporary Tables due to dynamic SQL

Currently we have a query that forecast income for direct debit products. This Query uses permanent tables opposed to temp tables as the query use Dynamic SQL and temp tables could not be seen outside the scope of that dynamic SQL execution. What I…
Jackt153
  • 1
  • 1
0
votes
1 answer

teradata - volatile table

2 Questions I populated a volatile table with couple of million rows and am running a query via bteq on a Solaris Client ( so it runs regardless of my workstation connection) . Now If I see the run time explain stuck at some step , I want to abort…
user1874594
  • 2,277
  • 1
  • 25
  • 49
0
votes
1 answer

Global temp tables getting dropped in Microsoft Sql Server

We have set of global temporary tables which are created in SP and filled. And that's kind of main sp which is called from application. Now say i am the first user who call's the main SP the global temporary tables get created and everything works…
user3724852
  • 1
  • 2
0
votes
0 answers

C# Populating GTT does not preserve rows

I have a package that allows me to create a table from the output of a stored procedure REF_CURSOR variable. I use this with DevPress XPO Source in order to return large results to my client application. I used to create a solid table, add a key,…
Jaques
  • 2,215
  • 1
  • 18
  • 35
0
votes
1 answer

What is the Difference between global temp table and local temp table in SYBASE ASE 15

What is the Difference between global temp table and local temp table SYBASE ASE 15 ? And how both would behave when access multiple time from java with different thread/connection/db method invocation ? Global temp table's starts with ## and local…
barun
  • 393
  • 1
  • 5
  • 19
0
votes
0 answers

ON COMMIT PRESERVE ROW not working

I want to load a data in temp table in DB2. in syntax when i use ON COMMIT PRESERVE ROWS compiler throws an error: Lookup Error - DB2 Database Error: ERROR [42601] [IBM][DB2/AIX64] SQL0104N An unexpected token "ON" was found following "P BY…
Rade
  • 43
  • 2
  • 6
0
votes
2 answers

Global Temporary Table

Help me understand how Global temporary table works I have process which is going to be threaded and requires data visible only to that thread session. So we opted for Global Temporary Table. Is it better to leave global temporary table not being…
user3723562
  • 73
  • 1
  • 2
  • 15
0
votes
2 answers

OracleDataAdapter with GlobalTemporaryTable: FillSchema not working

I'm using FillSchema on a OracleDataAdapter in .net. The SelectCommand works on a global temporary table. For the first run, this works great. I will get the schema of the global temporary table Then I drop that temporary table and great a new…
DanielG
  • 1,217
  • 1
  • 16
  • 37
0
votes
2 answers

SQL Server Global Temporary Table Locking

How do I lock a global temporary table in a stored procedure that's getting created and populated by a SELECT INTO statement? For example: SELECT * INTO ##TempEmployee FROM Employee This stored procedure is executed for generating reports…
Shyam Sundar Ananthaswamy
  • 223
  • 1
  • 3
  • 10
0
votes
2 answers

MultiThreading in Java causing Table lock

I am doing insertion into a Oracle table using batchUpdate. Five different parallel threads are invoking batchUpdate method to insert into a single table. Table is of Global Temporary Table type. Program execution is not completing, its getting…
Mithun Khatri
  • 636
  • 3
  • 9
  • 22
-1
votes
2 answers

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

I have the following issue in SQL Server, I have some code that looks like this: IF OBJECT_ID('tempdb..##TempLog') IS NULL CREATE TABLE ##TempLog(id INT, name NVARCHAR(max)); INSERT INTO ##TempLog (id, name) VALUES (1, 'Test') When I do this…
Arash Kh
  • 39
  • 1
  • 5
-1
votes
1 answer

Select into #TempTable from @Variable results to Cartesian Product

Insert a table variable into a TempTable with multiple columns (ID, Number, etc.) One of the table variables is: DECLARE @@gvTempTable TABLE (Number bigint, ID int) insert into @@gvTempTable (Number) values ('21212321332332'); insert into …
Data Engineer
  • 795
  • 16
  • 41
-1
votes
1 answer

Access Oracle GTT table data from another session?

I am using Oracle GTT tables with condition ON COMMIT DELETE ROWS. Generally GTT table's data is only session specific. One session cannot see the GTT data of other session in general. But is their any way to access/read GTT table that belongs to…
Dhruv
  • 10,291
  • 18
  • 77
  • 126
1 2 3 4
5