¿La mejor manera de realizar una inserción de varias filas en Oracle?

Estoy buscando una buena manera de realizar inserciones de varias filas en una base de datos Oracle 9. Lo siguiente funciona en MySQL pero no parece ser compatible con Oracle.

INSERT INTO TMP_DIM_EXCH_RT 
(EXCH_WH_KEY, 
 EXCH_NAT_KEY, 
 EXCH_DATE, EXCH_RATE, 
 FROM_CURCY_CD, 
 TO_CURCY_CD, 
 EXCH_EFF_DATE, 
 EXCH_EFF_END_DATE, 
 EXCH_LAST_UPDATED_DATE) 
VALUES
    (1, 1, '28-AUG-2008', 109.49, 'USD', 'JPY', '28-AUG-2008', '28-AUG-2008', '28-AUG-2008'),
    (2, 1, '28-AUG-2008', .54, 'USD', 'GBP', '28-AUG-2008', '28-AUG-2008', '28-AUG-2008'),
    (3, 1, '28-AUG-2008', 1.05, 'USD', 'CAD', '28-AUG-2008', '28-AUG-2008', '28-AUG-2008'),
    (4, 1, '28-AUG-2008', .68, 'USD', 'EUR', '28-AUG-2008', '28-AUG-2008', '28-AUG-2008'),
    (5, 1, '28-AUG-2008', 1.16, 'USD', 'AUD', '28-AUG-2008', '28-AUG-2008', '28-AUG-2008'),
    (6, 1, '28-AUG-2008', 7.81, 'USD', 'HKD', '28-AUG-2008', '28-AUG-2008', '28-AUG-2008');

Sep 02 '08 20:09 jamz

En Oracle, para insertar varias filas en la tabla t con las columnas col1, col2 y col3, puede utilizar la siguiente sintaxis:

INSERT ALL
   INTO t (col1, col2, col3) VALUES ('val1_1', 'val1_2', 'val1_3')
   INTO t (col1, col2, col3) VALUES ('val2_1', 'val2_2', 'val2_3')
   INTO t (col1, col2, col3) VALUES ('val3_1', 'val3_2', 'val3_3')
   .
   .
   .
SELECT 1 FROM DUAL;

En Oracle 23c, puede insertar varias filas con esta sintaxis simplificada:

INSERT INTO t(col1, col2, col3) VALUES
('val1_1', 'val1_2', 'val1_3'),
('val2_1', 'val2_2', 'val2_3'),
('val3_1', 'val3_2', 'val3_3');

Para insertar una gran cantidad de filas, la nueva sintaxis es mucho más rápida que el INSERT ALLenfoque anterior y casi tan rápida como este UNION ALL. Sin embargo, debido al aumento exponencial de los tiempos de análisis, aún desea evitar insertar más de 1000 filas a la vez.

Sep 18 '2008 15:09 Myto

Esto funciona en Oracle:

insert into pager (PAG_ID,PAG_PARENT,PAG_NAME,PAG_ACTIVE)
          select 8000,0,'Multi 8000',1 from dual
union all select 8001,0,'Multi 8001',1 from dual

Lo que hay que recordar aquí es utilizar la from dualdeclaración.

Sep 02 '2008 14:09 Espo

Utilice el cargador SQL*. Se necesita un poco de configuración, pero si esto no es único, vale la pena.

Crear mesa

SQL> create table ldr_test (id number(10) primary key, description varchar2(20));
Table created.
SQL>

Crear CSV

oracle-2% cat ldr_test.csv
1,Apple
2,Orange
3,Pear
oracle-2%

Crear archivo de control del cargador

oracle-2% cat ldr_test.ctl 
load data

 infile 'ldr_test.csv'
 into table ldr_test
 fields terminated by "," optionally enclosed by '"'              
 ( id, description )

oracle-2%

Ejecute el comando SQL*Loader

oracle-2% sqlldr <username> control=ldr_test.ctl
Password:

SQL*Loader: Release 9.2.0.5.0 - Production on Wed Sep 3 12:26:46 2008

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Commit point reached - logical record count 3

Confirmar inserción

SQL> select * from ldr_test;

        ID DESCRIPTION
---------- --------------------
         1 Apple
         2 Orange
         3 Pear

SQL>

SQL*Loader tiene muchas opciones y puede tomar prácticamente cualquier archivo de texto como entrada. Incluso puedes insertar los datos en tu archivo de control si lo deseas.

Aquí hay una página con más detalles -> SQL*Loader

Sep 03 '2008 02:09 Matthew Watson

Siempre que necesito hacer esto, construyo un bloque PL/SQL simple con un procedimiento local como este:

declare
   procedure ins
   is
      (p_exch_wh_key INTEGER, 
       p_exch_nat_key INTEGER, 
       p_exch_date DATE, exch_rate NUMBER, 
       p_from_curcy_cd VARCHAR2, 
       p_to_curcy_cd VARCHAR2, 
       p_exch_eff_date DATE, 
       p_exch_eff_end_date DATE, 
       p_exch_last_updated_date DATE);
   begin
      insert into tmp_dim_exch_rt 
      (exch_wh_key, 
       exch_nat_key, 
       exch_date, exch_rate, 
       from_curcy_cd, 
       to_curcy_cd, 
       exch_eff_date, 
       exch_eff_end_date, 
       exch_last_updated_date) 
      values
      (p_exch_wh_key, 
       p_exch_nat_key, 
       p_exch_date, exch_rate, 
       p_from_curcy_cd, 
       p_to_curcy_cd, 
       p_exch_eff_date, 
       p_exch_eff_end_date, 
       p_exch_last_updated_date);
   end;
begin
   ins (1, 1, '28-AUG-2008', 109.49, 'USD', 'JPY', '28-AUG-2008', '28-AUG-2008', '28-AUG-2008'),
   ins (2, 1, '28-AUG-2008', .54, 'USD', 'GBP', '28-AUG-2008', '28-AUG-2008', '28-AUG-2008'),
   ins (3, 1, '28-AUG-2008', 1.05, 'USD', 'CAD', '28-AUG-2008', '28-AUG-2008', '28-AUG-2008'),
   ins (4, 1, '28-AUG-2008', .68, 'USD', 'EUR', '28-AUG-2008', '28-AUG-2008', '28-AUG-2008'),
   ins (5, 1, '28-AUG-2008', 1.16, 'USD', 'AUD', '28-AUG-2008', '28-AUG-2008', '28-AUG-2008'),
   ins (6, 1, '28-AUG-2008', 7.81, 'USD', 'HKD', '28-AUG-2008', '28-AUG-2008', '28-AUG-2008');
end;
/

Sep 18 '2008 10:09