SQL - Como hacer una actualizacion/correccion de una tabla en base a otra

Estimados amigos:

Voy a tratar de ser lo mas claro posible:

Tengo dos tablas de totales diarios: ventas_por_codigo y ventas_por_tienda_codigo.

Cada que se hace una venta se actualizan las 2 dos. Si al final del dia comparo las dos tablas me coinciden los totales por código.

En la madrugada tengo un proceso (stored procedure) que revisa y corrige y/o aumenta y/o borra registros que por alguna razon no coincidan con el total que calcula en base a las notas de venta menos las devoluciones. La tabla que revisa/corrige es la ventas_por_tienda_codigo.

Las correcciones que se hacen en esta tabla no se reflejan en la tabla ventas_por_codigo y normalmente esto hace que los números no coincidan si totalizo por código en la tabla corregida.

Existe alguna forma "sencilla" (stored procedure) en la cual se pueda corregir la tabla ventas_por_codigo acumulando para todas las tiendas de la tabla que fue corregida y que modifique los datos incorrectos, aumente registros que falten y borre registros que sobren ??

El stored procedure que funciona en la mañana lo que hace es que construye una tabla temporal con los totales de ventas menos devoluciones por tienda/codigo y luego hace 3 comparaciones: si falta algun registro lo aumenta, si sobra algun registro lo borra y si algun total no coincide lo corrige.

He pensado en varias opciones:

a) Que despues de que revise/corrija la tabla ventas_por_tienda_codigo haga las mismas 3 comparaciones con la otra tabla.

b) Borrar todos los registros del dia en la tabla ventas_por_codigo y agregarlas totalizando de la tabla ventas_por_tienda_codigo totalizando todas las tiendas.

Pero hago uso de este medio para ver si existe alguna forma que no conozca de manera que pueda resolver mi problema más facilmente.

Gracias de antemano por su tiempo.

Ing. Roberto Segoviano

pd. Cualquier duda con mi planteamiento estoy a sus ordenes.

¿Que motor de base de datos esta manejando?

¿Cual es la estructura de sus tablas?

¿Porque no hace una VISTA en base a la tabla original?

¿Que motor de base de datos esta manejando?
MS Sql Server 2014

¿Cual es la estructura de sus tablas?
Por simplificar vamos a poner lo siguiente:
Ventas_por_tienda_codigo:
fecha
tienda
codigo
ventas piezas
ventas pesos

Ventas_por_codigo:
fecha
codigo
ventas piezas
ventas pesos



¿Porque no hace una VISTA en base a la tabla original?
La tabla original en realidad son 2 tablas: ventas y devoluciones, y por la cantidad de registros era muy lento hacer consultas sobre estas tablas y por eso no vimos esta opción. Por eso cada que se hace una venta o devolucion se actualizan las dos tablas. Al final del día existe un procedimiento almacenado que revisa que la tabla ventas_por_tienda_codigo en base a acumular las tablas ventas y devoluciones en una tabla temporal y modificar/borrar/agregar registros para que coincida. Quiero ver si existe un solo Update o algo parecido para en base a esa tabla ya corregida corregir la otra ventas_por codigo si es que existe una manera sencilla, si no, hacer lo que hace el procedimiento almacenado, crear una tabla temporal con las ventas por codigo y luego modificar registros diferentes, agregar registros que falten y borrar registros que sobren. o la otra opción que sería borrar todos los registros del día y agregarlos en base a esa tabla temporal.

Espero haber sido claro.

Gracias
Ing. Roberto Segoviano

Roberto

Sin tener datos de las tablas, me es dificil hacer un ejercio, pero puede probar el siguiete codigo con TABLAS de paso

Le he colocado comentarios antes de cada rutina, espero y sirva como un INICIO para resolver su proceso.

Para que tenga un buen tiempo de respuesta, debera verificar que existan los indices pertinentes.

Muchas gracias por tu respuesta.

Necesito un poco de tiempo para analizar el codigo que me propones porque es algo que no sabia que existia el EXCEPT y el MERGE.

En lo que se esta haciendo actualmente es que no se crea la tabla de diferencias sino que se crea una tabla con los totales y sobre esa se compara.

Lo que se hacia para checar la tabla que si se verifica es 3 instrucciones diferentes : una para insertar registros faltantes, otra para borrar registros sobrantes y otra para actualizar totales incorrectos, y aqui me propones una sola instruccion MERGE con los 3 casos.

Gracias y dame un tiempo por favor para analizar esto

Con gusto

Lo que se hace al "obtener las diferencias" con EXCEPT, es muy simple, tengo DOS conjuntos de datos y lo que hace EXCEPT es solo tomar la diferencia del TARGET en comparacion con el SOURCE, eso es, para no incluir una rutina que vaya checando registro por registro y de igual forma, tome las diferencias, EXCEPT lo hace de forma muy eficiente.

MERGE, toma esas diferencias y dependiendo del MATCHED, decide si hacer un INSERT, UPDATE o DELETE, tambien lo hace de forma muy eficiente.

Saludos

por lo que estoy viendo en la documentacion, la parte final del MERGE que pusiste como :

WHEN MATCHED AND target.[ventas pesos] - source.[ventas pesos] <= 0 -- Aplico el borrado

THEN DELETE;

No esta correcto, porque estas indicando que si existen los registros en las dos tablas y en realidad cuando hay que borrar es cuando existe en target y no existe en source.

Creo que deberia de ser lo siguiente :

WHEN NOT MATCHED BY SOURCE
THEN DELETE;

Porque de esa manera le estoy diciendo que lo que no coincide porque no se encuentra en el source (@MisDiferencias) entonces no debe de existir en en el target. y lo que tu pusiste dice que si existen los registros en las dos tablas (matched).

Tal como entendí es :

when matched : estan en las dos tablas pero hay diferencias en los totales de pares o pesos y hay que hacer update en taget

when not matched (by target) : no esta en la tabla target y si en source y por lo tanto hay que insertarlo en target

when not matched by source : no esta en la tabla source y si en la target, por lo tanto hay que borrarlo en target.

Espero tu comentario para ver si ando errado o no.

Gracias como siempre por tus comentarios.

Gracias Roberto

Todo es cuestion de las pruebas que realices y nos digas como te fue

Estimado Isaias:

Aprovechandome de tu generosidad, te solicito tu opinión con respecto a como trabaja en este momento el procedimiento almacenado que revisa la tabla de ventas_por_tienda_codigo antes de empezar a probar lo que me sugeriste.

El procedimiento almacenado hace lo siguiente todas las madrugadas:

Nota: cuando hable de ventas o devoluciones me refiero a piezas e importes y adicionalmente se tiene total por ventas al público, ventas internas entre tiendas y ventas totales (suma de ventas al publico mas ventas internas). Las ventas/devoluciones al publico estan como cliente 5001 y las ventas/devoluciones internas estan con el cliente <=5000 (aunque solo manejamos hasta el 500).

PROCEDIMIENTO ALMACENADO:

1.- Crea una tabla temporal #tmp_ventas

2.- Puebla esta tabla con cross joins entre las tablas codigos y tiendas, esto es, crea un registro para cada codigo de cada tienda con los totales de ventas y devoluciones en cero

Los siguientes pasos son hechos con la tabla #tmp_ventas:

3.- Hace un update con las ventas al publico (cliente=5001) de la tabla de notas de venta

4.- Hace un update con las ventas a clientes internos (cliente <=5000) de la tabla de notas de venta

5.- Hace un update de las devoluciones al publico (cliente=5001) de la tabla notas de devolucion

6.- Hace un update de las devoluciones a clientes internos (cliente<=5000) de la tabla notas de devolucion

7.- Hace un update para calcular las ventas y devoluciones totales (suma de publico mas clientes internos)

Hasta aqui los pasos anteriores eran para calcular datos y colocarlos en la tabla #tmp_ventas y a partir de aqui empieza la correccion de la tabla ventas_por_tienda_codigo en base a los datos recien calculados que estan en la tabla #tmp_ventas:

8.- Se hace un insert para agregar los registros que no existen en la tabla ventas_por_tienda_codigo y si existen en la tabla #tmp_ventas

9.- Se hace un update en la tabla ventas_por_tienda_codigo para los registros que tienen totales diferentes

10.- Se borra la información que existe en la tabla ventas_por_tienda_codigo y que no existen en #tmp_ventas

11.- Se borra la informacion que pudiera haber llegado a quedar con totales en cero de la tabla ventas_por_tienda_codigo (no se si realmenta haga algo este paso)

FIN PROCEDIMIENTO ALMACENADO

Y hasta aqui es lo que hace el procedimiento almacenado actual que hace el trabajo requerido pero no se si se pueda optimizar, debido a que normalmente tarda 2 horas y 25 minutos y todavia sin checar la tabla ventas_por_codigo.

Estaba pensando en que si antes de empezar a corregir (paso 7a) se crea una tabla temporal o de variables con solo las diferencias y posteriormente se hace ya sea insert/update/delete o un merge con la misma función pero solo con la tabla de diferencias en lugar de con la tabla #tmp_ventas que contiene todo, probablemente valdria la pena pagar el precio de construir dicha tabla de solo diferencias y que las comparaciones fueran sobre una tabla pequeña con solo diferencias y no busque las diferencias sobre tablas mas grandes.

Probablemente no se necesario el paso 2 de poblar la tabla con un cross join sino que en los pasos 3, 4, 5 y 6 hacer MERGE para ir haciendo updates o inserts segun se vayan necesitando en la tabla #tmp_ventas.

Es lo que por el momento me brinca y por lo cual pido tu opinión/asesoria para ver que es lo más conveniente.

Como siempre muchas gracias por tu apoyo y tu tiempo.

Ing. Roberto Segoviano

pd espero haber sido claro

Fuiste muy claro

Debo mencionarte que conozco SQL Server desde la version 4.0 (no salio al mercado) y hasta la 2017, pasando a mi opinion

SQL Server, como MUCHOS otros motores, son muy "TORPES" para manejar registro a registro, son muy eficientes manejando grandes cantidades de datos.

Veo, en mi humilde opinion, que ese es el problema que tienes, por eso es que se tarda tanto.

Con el query que te di, debe tardarse MINUTOS, 10 para mi, ya serian DEMASIADOS.

Te lo digo, porque lo acabo de probar aca en la CD. DE MEXICO con un cliente y de 17 horas que se tardaba su proceso, lo bajamos a 15 minutos (y se me hace aun mucho).

Saludos

Estimado Isaias:

Otra pregunta relacionada con esto:

En un merge se puede poner un Select como source , a lo que me refiero es a :

MERGE ventas_por_dia_articulo AS TARGET
USING (SELECT .............FROM ...... WHERE ..... GROUP ......) AS TARGET
ON ..........

Gracias de antemano
Roberto Segoviano

Estimado Isaias:

Me respondo a mi mismo:

Si se puede poner un Select como Source

MERGE ventas_por_dia_articulo AS TARGET
USING (SELECT .............FROM ...... WHERE ..... GROUP ......) AS SOURCE
ON ..........

Ahora lo que no se puede hacer es poner un select como Target de la manera

MERGE (SELECT * FROM ventas_por_dia_articulo WHERE TARGET.fecha=@fecha) AS TARGET
USING (SELECT .............FROM ...... WHERE ..... GROUP ......) AS TARGET
ON ..........

Ahora mi nueva pregunta es como hacer de la manera mas eficiente este MERGE si el archivo ventas_por_dia_articulo contiene registros de varios años y en el SOURCE solo traigo las ventas de un solo dia y es lo unico que necesito checar.

Lo único que se me ocurre en que en el WHEN NOT MATCHED BY SOURCE poner:

WHEN NOT MATCHED BY SOURCE AND T.fecha=@fecha THEN
DELETE;

Esto es si esta en el target y no est en el source y es de la fecha que estoy checando entonces borrar. Si no pongo est condición entonces me borraría todos los registros que no sean del dia a checar ( que es lo que traigo en el Source).

Alguna mejor opinion?

Gracias de antemano
Ing. Roberto Segoviano