una - insertar registro auto_increment sql

Copie registros con Insertar selección, luego actualice atributos en el mismo conjunto de registros

Question

Tengo un procedimiento de Oracle en el que me gustaría hacer lo siguiente:

1. Copie registros de una tabla a otra
2. Actualice los registros originales para indicar que se han copiado

Actualmente , estoy usando un ciclo FOR para procesar cada registro individualmente. Esto funciona, pero es mucho código para hacer algo que creo que podría ser más simple.

Me gustaría:

1. Elimine el bucle for y copie los registros con una instrucción INSERT INTO ... SELECT ...
2. Si la copia es exitosa, actualice todos los registros seleccionados.

Esto puede parecer simple, solo un INSERT y una instrucción UPDATE con la misma cláusula WHERE. Sin embargo, en el tiempo transcurrido entre la ejecución de las dos declaraciones, se pueden insertar más registros que deben copiarse. Si utilizo la misma cláusula WHERE, puedo marcar registros como procesados ​​que no se han procesado realmente.

¿Hay alguna manera de que pueda guardar una lista de las claves principales para usar en ambas declaraciones o fusionar las declaraciones? ¿O recomendaría que me quede con el ciclo FOR y procese los registros de a uno por vez? Toda la entrada constructiva bienvenida.

Answer 1

La respuesta de Rajesh debería funcionar bien si hay una pequeña posibilidad de que la tabla fuente se actualice al mismo tiempo. Si la tabla fuente se actualiza con frecuencia, la transacción serializable fallará con demasiada frecuencia.

Otro enfoque sería usar la consulta de retrospectiva para obtener un vistazo puntual a la tabla. La sintaxis es una forma conveniente de ver cómo era una tabla en el pasado (reciente).

declare
    v_current_scn number;
begin
    v_current_scn := dbms_flashback.get_system_change_number;

    insert into tgt..
    select * from src
    where <condition>
        and row_status <> ''copied''; --ignoring records after they have been copied once?

    update src
    set row_status = ''copied''
    where rowid in
        (
            select rowid
            from src as of scn v_current_scn
            where <same_where_condition_as_before>
        );
end;
/

Answer 2

Si ejecuta las dos instrucciones within a transaction , cualquier falla ocasionará que la inserción y la actualización se retrotraigan.

insert into tgt..
select * from src
where <condition>
  and row_status <> ''copied''; --ignoring records after they have been copied once?

update src
set row_status = ''copied''
where <same_where_condition_as_before>

commit;

Si hay nuevas filas insertadas en la tabla de origen después de haberlas leído, es posible que deba ejecutar nuevamente el bloque, con una condición modificada donde sea apropiado.