sql - registros - rownum oracle

¿La mejor manera de hacer inserciones de varias filas en Oracle?

Question

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'');

Answer 1

Cuando 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;
/

Answer 2

En Oracle, para insertar varias filas en la tabla t con las columnas col1, col2 y col3 puede usar 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;

Answer 3

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 usar la declaración from dual .

( source )

Answer 4

Si ya tiene los valores que desea insertar en otra tabla, puede insertar desde una declaración de selección.

INSERT INTO a_table (column_a, column_b) SELECT column_a, column_b FROM b_table;

De lo contrario, puede enumerar un grupo de instrucciones de inserción de una sola fila y enviar varias consultas de forma masiva para ahorrar tiempo para algo que funciona tanto en Oracle como en MySQL.

La solución de @Espo también es buena y funcionará tanto en Oracle como en MySQL si sus datos aún no están en una tabla.

Answer 5

También se pueden usar cursores, aunque es ineficiente. La siguiente publicación de discute el uso de cursores:

INSERTAR y ACTUALIZAR un registro usando cursores en Oracle

Answer 6

Utilice SQL * Loader. Se necesita un poco de configuración, pero si este no es uno, 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 de 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% 

Ejecuta 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 insertar

SQL> select * from ldr_test;

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

SQL>

SQL * Loader tiene muchas opciones y puede tomar casi cualquier archivo de texto como entrada. Incluso puede insertar los datos en su archivo de control si lo desea.

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

Answer 7

puede insertar utilizando bucle si desea insertar algunos valores aleatorios.

BEGIN 
    FOR x IN 1 .. 1000 LOOP
         INSERT INTO MULTI_INSERT_DEMO (ID, NAME)
         SELECT x, ''anyName'' FROM dual;
    END LOOP;
END;