Inserciones en más de una tabla con ‘INSERT’ (‘INSERTs’ multitablas).

Existe una funcionalidad bastante desconocida en Oracle que permite hacer inserciones en más de una tabla con una única sentencia ‘INSERT’ (Es curioso como ciertas funcionalidades utilísimas son casi desconocidas para la mayoría de la gente, pero esa es otra historia…)

El caso es que podemos hacer que un mismo ‘INSERT’ inserte filas en dos o más tablas, y además podemos hacer que esto se haga de forma condicional (si las filas cumplen ciertas condiciones) o incondicional (todas las filas).

También podemos hacer que si una fila es insertada en una tabla no se inserte en ninguna otra o, por el contrario, que todas las filas se inserten en todas las tablas.

Vamos con un ejemplo de inserción incondicional:

carlos@db01.joe> SELECT * FROM V$VERSION;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod
PL/SQL Release 10.2.0.3.0 - Production
CORE    10.2.0.3.0      Production
TNS for 32-bit Windows: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production

carlos@db01.joe> DESC PRUEBA01;
 Nombre                                    ¿Nulo?   Tipo
 ----------------------------------------- -------- -------------------------
 ID_N                                      NOT NULL NUMBER(38)
 C_TXT1                                             VARCHAR2(10)
 C_TXT2                                             VARCHAR2(10)

carlos@db01.joe> DESC PRUEBA02;
 Nombre                                    ¿Nulo?   Tipo
 ----------------------------------------- -------- -------------------------
 ID_N                                      NOT NULL NUMBER
 C_TXT                                              VARCHAR2(10)

carlos@db01.joe> DESC PRUEBA03;
 Nombre                                    ¿Nulo?   Tipo
 ----------------------------------------- -------- -------------------------
 ID_N                                      NOT NULL NUMBER
 C_TXT                                              VARCHAR2(10)

carlos@db01.joe> SELECT * FROM PRUEBA01;

      ID_N C_TXT1     C_TXT2
---------- ---------- ----------
         1 UNO        ONE
         2 DOS        TWO
         3 TRES       THREE

carlos@db01.joe> INSERT ALL
  2  INTO PRUEBA02 (ID_N, C_TXT)
  3  VALUES (ID_N, C_TXT1)
  4  INTO PRUEBA03 (ID_N, C_TXT)
  5  VALUES (ID_N, C_TXT2)
  6  SELECT ID_N, C_TXT1, C_TXT2
  7  FROM PRUEBA01;

6 filas creadas.

carlos@db01.joe> SELECT * FROM PRUEBA02;

      ID_N C_TXT
---------- ----------
         1 UNO
         2 DOS
         3 TRES

carlos@db01.joe> SELECT * FROM PRUEBA03;

      ID_N C_TXT
---------- ----------
         1 ONE
         2 TWO
         3 THREE

carlos@db01.joe>

Y ahora vamos con un ejemplo de inserción condicional (los pares a una tabla, los impares a otra):

carlos@db01.joe> SELECT ID_N, C_TXT1 FROM PRUEBA01;

      ID_N C_TXT1
---------- ----------
         1 UNO
         2 DOS
         3 TRES

carlos@db01.joe> INSERT ALL
  2  WHEN MOD(ID_N,2) = 0 THEN
  3       INTO PRUEBA02 (ID_N, C_TXT)
  4       VALUES (ID_N, C_TXT1)
  5  WHEN MOD(ID_N,2) != 0 THEN
  6       INTO PRUEBA03 (ID_N, C_TXT)
  7       VALUES (ID_N, C_TXT1)
  8  SELECT ID_N, C_TXT1
  9  FROM PRUEBA01;

3 filas creadas.

carlos@db01.joe> SELECT * FROM PRUEBA02;

      ID_N C_TXT
---------- ----------
         2 DOS

carlos@db01.joe> SELECT * FROM PRUEBA03;

      ID_N C_TXT
---------- ----------
         1 UNO
         3 TRES

carlos@db01.joe>

Una cosa importante a tener en cuenta es que los INSERTs multitabla tienen restricciones. Quizá la más importante es que no se pueden hacer este tipo de inserciones sobre tablas remotas (vía db-links). Lo curioso es que, si lo intentamos, el mensaje que aparece dista mucho de arrojar luz sobre el asunto:

ERROR en línea 3:
ORA-02021: las operaciones DDL no están permitidas en una base de datos remota

¡El analizador sintáctico de SQL toma estos comandos por DDL!

Saludos.

Carlos.

Anuncios

5 Responses to Inserciones en más de una tabla con ‘INSERT’ (‘INSERTs’ multitablas).

  1. Oscar de la Torre dice:

    Una par de aplicaciones interesantes para esta funcionalidad son:
    – utilizarla para realizar un “particionamiento manual” si no se dispone de la licencia para la opción de particionamiento
    – emplearla para dejar un log de registros rechazados en cargas masivas (este caso en versiones anteriores a la 10gR2)

  2. carlosal dice:

    Sin duda.

    Hacer un ‘particionamiento del pobre’ con una vista sobre las tablas que actúan como particiones y un ‘instead insert trigger’ que vaya colocando las filas en cada tabla según el ‘criterio de particionamento’ es una muy buena idea.

    Lo del log de registros no lo pillo…

    Saludos.

    Carlos.

  3. Oscar de la Torre dice:

    Algo como:

    INSERT ALL
    WHEN condicion_cumplida THEN
    INTO TABLA (C1, c2, … Cn)
    VALUES (c1, c2, … cn)
    WHEN condicion_no_cumplida THEN
    INTO TABLA_ERROR (c1, c2, … cn)
    VALUES (c1, c2, … cn)
    SELECT c1, c2, … cn
    FROM TABLA_EXTERNA;

  4. carlosal dice:

    Óscar:

    Ahora lo pillo.

    Lo que pasa es que no se pueden capturar los errores (excepciones) directamente, con lo que habría que hacer algún tipo de función que los capturara y devolviera TRUE/FALSE o algo así. (P.ej.: si una conjunción dia/mes/año en CHAR no correspondiera a una fecha válida con TO_DATE…).

    Sigues con las tablas externas eh? 😉 A mí lo que me mola de su uso es la facilidad de cargas usando PARALLEL (además de /*+ APPEND */ Y NOLOGGING). A ver si un día escribo algo sobre este tema…

    Un saludo.

    Carlos.

  5. Angel dice:

    Que buen aporte lo aplicare en posteriores creaciones gracias 😉

Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s

A %d blogueros les gusta esto: