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
2
votes
1 answer

How to manage global temporary tables in SP run inside transaction?

Running SQL Server 2014. I have a stored procedure where I need to write data to a global temporary table. However, it seems that the method OBJECT_ID hangs when the procedure is called inside a transaction. How do I remove the deadlock without…
l33t
  • 18,692
  • 16
  • 103
  • 180
2
votes
1 answer

Unable to select global temp table (##TempTable) from C#

When I try to select global temp table from win form application then I am able to read the table from any number of client, when I do the same from WCF service hosted in windows service, I am getting error saying that Invalid object name…
Dev
  • 960
  • 2
  • 15
  • 35
1
vote
1 answer

How to creating a global temp table in one stored procedure, and use it in another stored procedure

I have a dynamic stored procedure that returns a result set. CREATE OR ALTER PROCEDURE [test].[proc] (@id INT, @temp_table_name VARCHAR(50) = '') AS BEGIN SET @sql = N'SELECT * FROM test.table con'; IF (LEFT(@temp_table_name, 2)…
Siddharth Dinesh
  • 345
  • 4
  • 13
1
vote
0 answers

Error on executing Global temp tables in SSIS package on server

I have an SSIS package which is as below.The DelayValidation Property of data flow task is TrueThe DelayValidation Property of all Execute SQL task is True The RetainSameConnection Property of Source connection task is True The Package runs fine on…
Meen
  • 119
  • 3
  • 15
1
vote
1 answer

SQL Temporary Table Issue

I've created a temporary table DETAILS and follow the same syntax of creating and inserting in it. But I have not received any result set However, the CREATE and INSERT statements ran successfully and the Row was also affected in the INSERT…
Ali
  • 35
  • 1
  • 5
1
vote
2 answers

what is the difference between external tables and global temporary tables in oracle?

I have worked with external tables in oracle, It can be created on a file containing data (with many other conditions). Then, How global temporary tables are different from External tables ?
it_iz_code
  • 43
  • 1
  • 9
1
vote
1 answer

Data Type Conversion causing error in SSIS Package

I have an SSIS package that has a step that creates a temp table using a Execute SQL Task. Inside of this query I have a case statement that is something like: Cast(Case When billing_address is Like '%DONOTUSE%' Then 1 When billing_address…
vinylmike
  • 23
  • 3
1
vote
3 answers

When and Why to use global temporary table over local temporary table in SQL Server

I have a local temporary table like this: create table #tbl ( employeeId int, employeeName nvarchar(50) ) insert into #tbl values (1, 'a'), (2, 'b'), (3, 'c'), (4, 'd') select * from #tbl Now I can access the local temporary table…
Lijin Durairaj
  • 4,910
  • 15
  • 52
  • 85
1
vote
2 answers

ssis temp table exec proc

SSIS newbie here. I have an SSIS package I created based on the wizard. I added a SQL task to run the script I was running previously separately, in order to reduce the process to one step. The script uses lots of temp tables, and one global…
hmmmmm
  • 15
  • 8
1
vote
0 answers

SQL Developer: Select query results has the expected row but doesn't display it on the grid

I am using SQL developer 3.2.2 to query an Oracle 12 database. I have a select query where I am expecting a certain row to be picked by the query. The results of this query is moved to a global temp table for further processing. But when I query the…
Kris Doe
  • 13
  • 4
1
vote
1 answer

Global Temp Table Oracle

New for Oracle. I have used the global temporary table in Oracle. This temp table stores the input values from the front end, and we further process the data. So my question is that since multiple users will send the requests how will store the…
Pankaj Kumar
  • 655
  • 3
  • 10
  • 16
1
vote
1 answer

Global Temporary table drops after another recordset is opened

I currently open and fill a global table with the name ##GlobalTableMain via a macro at the moment. The table gets created the following way: Public Sub ExecuteSQLQuery(sQuery As String) Dim cn As New ADODB.Connection cn.Open…
Spurious
  • 1,903
  • 5
  • 27
  • 53
1
vote
1 answer

DB2 Stored Procedures- looping through values?

Okay, so I'm a novice at writing stored procedures. I'm trying to perform a function similar to a foreach() you would see in a programming language. Right now I have a temp table populated with the values I'd like to loop through. I would like to…
MAS
  • 13
  • 1
  • 3
1
vote
0 answers

Column name or number of supplied values does not match table definition during execution plan on

Currently we are facing the issue only when we are executing a stored procedure keeping the Include Actual Execution Plan - ON. Otherwise the stored procedure is executing fine and returning results as expected. What would be the reason for this…
Abhishek
  • 2,482
  • 1
  • 21
  • 32
1
vote
1 answer

Create temporary tables in Oracle stored procedure to show in Crystal Reports

In SQL Server I can create stored procedures which creates a temp table, insert values into it, and then return a select from that temp table to be the result set for a composite Crystal Report. I have no idea how to perform it in Oracle stored…
Dantevip
  • 33
  • 3
1
2
3 4 5