SQL Server / Oracle : Private temporary tables

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.

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

select *into #temp_tablefrom mytable

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 codeCREATE 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 codeCREATE TABLE #KKsum(FUND_CODE char(5),KKAMT decimal(19,2) )INSERT INTO #KKsum (FUND_CODE, KKAMT) select FUND_CODE, sum(POSTED_TOTAL_AMT  * -1) KKAMTfrom 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 amountselect #KKsum.FUND_CODE, JRNLAMT, KKAMT from #JRNLsumINNER JOIN #KKsumon #KKsum.FUND_CODE=#JRNLsum.FUND_CODE where (JRNLAMT - KKAMT) <> 0.00--drop the two temp tablesdrop table #KKsumdrop table #JRNLsum/*** END NESTED QUERY #1