sql server referencial TSQL-instrucción MERGE con clave compuesta



integridad referencial sql server (2)

Para los principiantes, solo las columnas de la tabla de destino se pueden usar en la condición de fusión adicional WHEN NOT MATCHED BY SOURCE (está en MSDN ).

Y creo que es normal que pierda todas las entradas adicionales de la tabla objetivo, porque no coinciden con nada en la fuente.

Debe volver a escribir su consulta eliminando primero la cláusula WHEN NOT MATCHED BY SOURCE y luego eliminando por separado las filas extra / innecesarias.

Luego, necesita obtener todas las entradas que se actualizan o insertan en la tabla de destino agregando:

DECLARE @OutputTable table( OrderId INT, OrderLine INT);

...Your entire MERGE
WHEN NOT MATCHED BY TARGET THEN
    INSERT
        ([OrderID], [LineIndex], [Data])
    VALUES
        ([Source].[OrderID], [Source].[LineIndex], [Source].[Data])
OUTPUT INSERTED.OrderId, INSERTED.LineIndex INTO @OutputTable

Ahora en @OutputTable tiene todas las claves que se actualizaron o se ingresaron en la tabla de destino (observe la cláusula OUTPUT ).

Ahora solo necesita ver qué filas de la tabla de destino, que solo coinciden con las claves de @OrderLines , no están en @OutputTable' and delete them (so they haven't been updated nor inserted by the instrucción MERGE`):

DELETE A
FROM [OrderLines] AS A
INNER JOIN @OrderLines AS B
 ON B.OrderId = A.OrderId AND B.LineIndex = A.LineIndex
LEFT OUTER JOIN @OutputTable AS C
 ON C.OrderId = A.OrderId AND C.OrderLine = A.LineIndex
WHERE C.OrderId IS NULL AND C.OrderLine IS NULL 

Lo que estás haciendo aquí (creo que es correcto) es en realidad lo que querías borrar en primer lugar. La combinación interna filtra el conjunto de resultados a @OrderLines (de modo que solo las filas con esas teclas) y la izquierda se unen con la cláusula where hace una unión anti semi, para obtener filas en la tabla de destino que no MERGE afectadas por la instrucción MERGE (insertar o actualizar) pero aún tienen claves que están en la tabla fuente ( @OrderLines ).

Debería estar en lo cierto ... Házmelo saber después de que lo pruebes.

Es posible que desee envolver todo esto ( MERGE + DELETE ) dentro de una transacción, si decide seguir este enfoque.

https://src-bin.com

Tengo table OrderLines (OrderID int, LineIndex int,) y un parámetro de tabla de la misma estructura que define nuevas líneas de orden para una orden.

Entonces, si tuviera las siguientes líneas de pedido

1000   1   bread
1000   2   milk
1001   1   oil
1001   2   yogurt
1002   1   beef
1002   2   pork

y el siguiente TVP

1001   1   yogurt

Quiero obtener las siguientes líneas de pedido

1000   1   bread
1000   2   milk
1001   1   yogurt
1002   1   beef
1002   2   pork

Es decir, toque las filas solo para una orden.

Así que escribí mi consulta de esta manera

MERGE
    [OrderLines] AS [Target]
USING
(
    SELECT
        [OrderID], [LineIndex], [Data]
    FROM
        @OrderLines
)
AS [Source] ([OrderID], [LineIndex], [Data])
ON ([Target].[OrderID] = [Source].[OrderID]) AND ([Target].[LineIndex] = [Source].[LineIndex])
WHEN MATCHED THEN
    UPDATE
    SET
        [Target].[Data] = [Source].[Data]
WHEN NOT MATCHED BY TARGET THEN
    INSERT
        ([OrderID], [LineIndex], [Data])
    VALUES
        ([Source].[OrderID], [Source].[LineIndex], [Source].[Data])
WHEN NOT MATCHED BY SOURCE THEN
    DELETE;

y borra todas las otras (no mencionadas) OrderLines para otras Órdenes.

Lo intenté

WHEN NOT MATCHED BY SOURCE AND ([Target].[OrderID] = [Source].[OrderID]) THEN

pero obtuve un error sintáctico.

¿Cómo debería reescribir mi consulta?


Answer #1

Simplemente use el subconjunto relevante de OrderLines como un objetivo:

WITH AffectedOrderLines AS (
    SELECT *
    FROM OrderLines
    WHERE OrderID IN (SELECT OrderID FROM @OrderLines)
)
MERGE
    AffectedOrderLines AS [Target]
USING
(
    SELECT
        [OrderID], [LineIndex], [Data]
    FROM
        @OrderLines
)
AS [Source] ([OrderID], [LineIndex], [Data])
ON ([Target].[OrderID] = [Source].[OrderID]) AND ([Target].[LineIndex] = [Source].[LineIndex])
WHEN MATCHED THEN
    UPDATE
    SET
        [Target].[Data] = [Source].[Data]
WHEN NOT MATCHED BY TARGET THEN
    INSERT
        ([OrderID], [LineIndex], [Data])
    VALUES
        ([Source].[OrderID], [Source].[LineIndex], [Source].[Data])
WHEN NOT MATCHED BY SOURCE THEN
    DELETE;

Y aquí hay un SQL Fiddle para probar.





sql-server-2012