SQL Server / Oracle: tablas temporales privadas

En Oracle, puede crear una tabla temporal usando algo como:

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

… que podría ser bastante ingenioso, ya que esto crea una tabla visible para todos, pero los datos que uno inserta en la tabla son visibles solo para él o ella. Además, esos datos se eliminan automáticamente al final de la transacción o de la session (según su statement), dejando intactos los datos temporales de todos los demás.

En SQL Server, sin embargo, puede crear una tabla temporal con:

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

… que, según tengo entendido, es sustancial y funcionalmente diferente de la implementación de Oracle. Esta tabla temporal solo es visible para usted y se descarta (la tabla) inmediatamente después de su uso.

¿Hay alguna capacidad en SQL Server para imitar el comportamiento de Oracle como se describió anteriormente? ¿O la única forma de trabajar con datos temporales implica tener que CREAR repetidamente la tabla temporal con cada iteración de trabajo?

Como ha descubierto, las tablas temporales de SQL Server y Oracle son fundamentalmente diferentes.

En Oracle, las tablas temporales globales son objects permanentes que almacenan datos temporales específicos de la session (o específicos de la transacción).

En SQL Server, las tablas temporales son objects temporales que almacenan datos temporales, con #temp_tables almacenando datos que son locales a una session y ## temp_tables almacenando datos que son globales. (Nunca he tenido necesidad de tablas temporales globales de SQL Server y no sé qué problema resuelven). Si la #temp_table se creó en un procedimiento almacenado, se eliminará cuando el procedimiento almacenado finalice. De lo contrario, se eliminará cuando la session se cierre.

Y no, realmente no hay una manera de hacer que SQL Server imite a Oracle. Puede usar una tabla normal con una columna extra almacenando una ID de session. Pero no obtendría las ventajas de las tablas temporales con respecto a un menor logging. Tendría que eliminar manualmente los datos temporales. Y lidiar con la limpieza de las sesiones que cesan prematuramente.

EDITAR: Otra diferencia entre Oracle y SQL Server es que SQL Server permite que DDL se envuelva en una transacción con otras declaraciones. Por lo tanto, si necesita usar una tabla temporal como parte de una transacción más grande, la create table #table_name... no comprometerá implícitamente la transacción actual como haría una sentencia create table en Oracle.

Esto está fuera de tema, pero ¿sabías que en SQL Server puedes crear una tabla temporal como esta:

 select * into #temp_table from mytable 

Las tablas temporales en SQL pueden ser muy útiles cuando necesita combinar datos de diferentes fonts que tienen un campo de combinación común, pero donde debe sumr los importes antes de la fusión para comparar los totales netos de las dos fonts. En un sistema financiero que es útil. Me decepcionó cuando pasamos de SQL Server a Oracle porque perdí esa funcionalidad.

El siguiente ejemplo es para una implementación financiera de PeopleSoft. El module de presupuesto (tablas KK) y el libro mayor general (diario) deben tener los mismos saldos para un background una vez que la interfaz se haya ejecutado entre los dos. La consulta siguiente totaliza las cantidades presupuestarias por background de las tablas KK y las almacena en una tabla temporal, luego sum las cantidades correspondientes por background del libro mayor general, luego fusiona las dos tablas de datos pre-sumdas para permitir la comparación de la cantidad neta por background las dos fonts, y enumera los resultados solo cuando hay una diferencia entre los montos de un background. En ese caso, los modules de presupuesto y GL no están sincronizados. Esta es en realidad una solución bastante elegante y no hubo necesidad de crear una tabla temporal global disponible para otros para esta consulta / informe.

Espero que alguien encuentre esto útil. Me ayudó en ese momento.

 /*** 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