I am using Oracle 11g Global Temporary Tables as I need a solution where I can add rows to a temporary table for a join, and I want only the rows added to the temp table for the Oracle Connection/session to be included. I am using Global Temp Table in Oracle becuase I want the table to exist between sessions so it doesn't have to be recreated every time I create a query. This is working out fine.
My Oracle table definition is as follows:
CREATE GLOBAL TEMPORARY TABLE book_id_temp
(
book_id RAW(32)
)ON COMMIT DELETE ROWS;
I have the same database structure also on the SQL Server 2008-R2 side, and need a similar solution in SQL Server. I want to :
From what I've read of global temporary tables in SQL Server, the tables exist after a connection is ended, like a regular table, and like a Global Temp Table in Oracle. However, it's not clear on the scope of the data. Does only the SQL Server session that created the rows have access to it, like in Oracle? What is the accessability of the data with SQL Server Global Temp Tables? Do you have a suggestion of an alternative to achieve my goal?
Temporary tables in Oracle are permanent objects that hold temporary data that is session local. Temporary tables in SQL Server are temporary objects.
I find that a local temporary table, or table variable, is the closest to being the same to Oracle's global temp table, the big difference is you have to create it every time.
Usually, in a case like yours, step 3, add rows to temp table, would be done by doing a select ... into #temp_table_name ....
(equivalent to Oracle create table ... as select ...
) http://msdn.microsoft.com/en-us/library/ms188029.aspx
Also, you can't do the following in a stored proc: (pseudo code.)
begin proc
call another proc to create local temp table.
use temp table
end proc
Local temp tables are destroyed when returning from the stored procedure that created them.
Update 2014-10-14: The behavior of local temp tables is different in the Parallel Data Warehousev version of SQL Server. Temporary tables are not dropped on exit from the stored procedure that created them, and instead continue existing for the rest of the session. This behavior observed on:
select @@version
Microsoft SQL Server 2012 - 10.0.5108.1 (X64) Jun 24 2014 20:17:02 Copyright (c) Microsoft Corporation Parallel Data Warehouse (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
Temporary tables on SQL Server are local by default. The table will be dropped after the session ends. If you execute a script like:
create table #Foo (
FooID int
,FooCode1 varchar (20)
)
insert table #Foo (FooID, FooCode1)
values (1001, 'X')
insert table #Foo (FooID, FooCode1)
values (1002, 'Y')
select f.FooID
,f.FooCode1
,b.BarID
,b.BarCode1
from #foo f
join bar b
on bar.FooID = f.FooID -- (or whatever predicate)
The query will only return rows that join on what you inserted into #Foo in this session. #Foo is local to the session; you can have multiple sessions with their own #Foo temporary table with no worries about namespace collisions. When the session is closed the temporary table will be discarded. You can also explicitly drop #Foo after you've finished with it if you're using a persistent database connection (e.g. a client-server desktop app).
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With