SQL Server / Oracle : Private temporary tables

Question

In Oracle, you can create a temp table using something like:

CREATE GLOBAL TEMPORARY TABLE temp_table (
    field1 NUMBER,
    field2 NUMBER
)
ON COMMIT DELETE ROWS;

... which could be pretty nifty, as this creates a table visible to everyone, but the data one INSERTs into the table is visible only to him or her. Additionally, that data is automatically deleted on at the end of the transaction or the session (depending on its declaration), leaving everyone else's temporary data unharmed.

In SQL Server, however, you can create a temp table with:

CREATE TABLE #temp_table (field1 INT, field2 INT);

... which, as I understand it, is substantially and functionally different than Oracle's implementation. This temp table is visible only to you, and is dropped (the table) immediately after use.

Is there any capacity in SQL Server to mimic the Oracle behavior as described above? Or is the only way to work with temporary data involve having to repeatedly CREATE the temp table with each iteration of work?

Answer 1

This is off topic but did you know that in SQL Server you can create a temp table like this:

select *
into #temp_table
from mytable

Answer 2

Temp tables in SQL can be very useful when you need to merge data from different sources that have a common merge field, but where you need to sum amounts prior to merging in order to compare net totals for the two sources. In a financial system that's useful. I was disappointed when we moved from SQL Server to Oracle because I lost that functionality.

The example below is for a PeopleSoft financials implementation. The budget module (KK tables) and general ledger (journal) should have the same balances for a fund once the interface has been run between the two. The query below totals budget amounts by fund from the KK tables and stores those in a temp table, then totals corresponding amounts by fund from the general ledger , then merges the two pre-summed data tables to allow comparison of the net amount per fund from the two sources--and lists results only when there is a difference between amounts for a fund. In that case, budget and GL modules are out of sync. This is actually a pretty elegant solution and there was no need to create a global temp table available to others for this query/report.

I hope someone finds this useful. It helped me at the time.

/*** START NESTED QUERY #1                                             ***/
/*** THE FOLLOWING CREATES TWO TEMP TABLES WITH NET AVAILABLE PER FUND ***/
/*** WITH ONE AMOUNT BASED ON KK TABLES AND ONE AMOUNT BASED ON        ***/
/*** BUDGETARY GL BALANCES. THEN TEMP TABLES ARE MERGED BY FUND AND    ***/
/*** NET DIFFERENCE CALCULATED-SELECTING  FUNDS WITH DIFFERENCES.      ***/
/*** IF BUDGET CHECKING IS COMPLETE AND JOURNALS CREATED AND POSTED    ***/
/*** THERE SHOULD BE NO DIFFERENCES.                                   ***/

--create a temp table with journal amounts summed by fund code
CREATE TABLE #JRNLsum(
FUND_CODE char(5),
JRNLAMT decimal(19,2) )
INSERT INTO #JRNLsum (FUND_CODE, JRNLAMT)
select FUND_CODE, sum(MONETARY_AMOUNT * -1) JRNLAMT 
FROM PS_JRNL_LN 
INNER JOIN PS_JRNL_HEADER 
ON PS_JRNL_LN.JOURNAL_ID = PS_JRNL_HEADER.JOURNAL_ID 
where ((ACCOUNT BETWEEN 430000 and 469999) and (FISCAL_YEAR >= '2009')) 
GROUP BY FUND_CODE order by FUND_CODE


--create a temp table with KK ledger amounts summed by fund code
CREATE TABLE #KKsum(
FUND_CODE char(5),
KKAMT decimal(19,2) )
INSERT INTO #KKsum (FUND_CODE, KKAMT) 
select FUND_CODE, sum(POSTED_TOTAL_AMT  * -1) KKAMT
from PS_LEDGER_KK where LEDGER like 'FUND_%'
group by FUND_CODE order by FUND_CODE

--join kk temp date to journal temp data, keep only
--fund code, kk net amount, and journal net amount
--and select only fund codes where there is a difference
--between kk net amount and journal net amount
select #KKsum.FUND_CODE, JRNLAMT, KKAMT from #JRNLsum
INNER JOIN #KKsum
on #KKsum.FUND_CODE=#JRNLsum.FUND_CODE 
where (JRNLAMT - KKAMT) <> 0.00


--drop the two temp tables
drop table #KKsum
drop table #JRNLsum

/*** END NESTED QUERY #1   

Answer 3

As you have discovered SQL Server & Oracle temporary tables are fundamentally different.

In Oracle global temporary tables are permanent objects that store temporary session specific (or transaction specific) data.

In SQL Server temporary tables are temporary objects storing temporary data, with #temp_tables storing data that is local to a session and ##temp_tables storing data that is global. (I have never had a need for SQL Server global temp tables and don't know what problem they solve.) If the #temp_table was created in a stored procedure it will be dropped when the stored procedure exits. Otherwise it will be dropped when the session closes.

And no, there really isn't a way to make SQL Server mimic Oracle. You could use a normal table with an extra column storing a session ID. But you wouldn't get the advantages of temp tables with respect to less logging. You'd have to manually delete the temp data. And deal with cleaning up from sessions that quit prematurely.

EDIT: Another difference between Oracle and SQL Server is that SQL Server allows DDL to be wrapped in a transaction with other statements. So if you need to use a temp table as part of a larger transaction, the create table #table_name... statement will not implicitly commit the current transaction like a create table statement would in Oracle.