Básicamente, quiero copiar la tabla DET en la tabla DET_NEW . Sé que DET tiene algunas filas duplicadas, por lo que quiero hacerlo insertando filas duplicadas solo una vez. Como puede ver, quiero copiar estas filas por períodos usando la tabla HDR .

Editar para agregar información: DET no tiene claves principales (es por eso que tiene filas duplicadas). DET_NEW tiene una clave principal compuesta con FK1 y FK2 .

PROBLEMA:

Sé que este no es un tema nuevo. He intentado todas las soluciones que he encontrado y SQL Server devuelve un error sobre la violación de restricción de clave primaria ("no se puede insertar clave duplicada"). Las consultas se ejecutaron explícitamente solo con un núcleo.

Vi que MySQL tiene INSERT IGNORE pero creo que no hay nada similar en SQL Server.

Mis soluciones son las siguientes:

Consulta 1:

INSERT INTO DET_NEW
    (FK1, FK2, value)
SELECT DISTINCT D.FK1, D.FK2, D.value
FROM HDR AS H,
    DET AS D
WHERE H.Date  >= CONVERT( datetime, '2015-01-01 00:00:00', 20 )
    and H.Date < CONVERT( datetime, '2016-01-01 00:00:00', 20 ) 
    and H.FK1 = D.FK1

Consulta 2:

INSERT INTO DET_NEW
    (FK1, FK2, value)
SELECT D.FK1, D.FK2, D.value
FROM 
(   SELECT DISTINCT D.FK1, D.FK2, D.value
    FROM HDR AS H,
        DET AS D
    WHERE H.Date  >= CONVERT( datetime, '2015-01-01 00:00:00', 20 )
        and H.Date < CONVERT( datetime, '2016-01-01 00:00:00', 20 ) 
        and H.FK1 = D.FK1
)D

Consulta 3:

INSERT INTO DET_NEW
    (FK1, FK2, value)
SELECT D.FK1, D.FK2, D.value
FROM HDR AS H,
        DET AS D
    WHERE H.Date  >= CONVERT( datetime, '2018-02-01 00:00:00', 20 )
        and H.Date < CONVERT( datetime, '2018-02-05 00:00:00', 20 ) 
        and H.FK1 = D.FK1
GROUP BY D.FK1, D.FK2

Consulta 4:

WITH cte AS (
    SELECT D.FK1, D.FK2, D.value,
        row_number() OVER(PARTITION BY D.FK1, D.FK2, D.value ORDER BY D.FK1) AS [rn]
    FROM HDR AS H,
        DET AS D
    WHERE H.Date  >= CONVERT( datetime, '2018-02-01 00:00:00', 20 )
        and H.Date < CONVERT( datetime, '2018-02-03 00:00:00', 20 ) 
        and H.FK1 = D.FK1
)

INSERT INTO DET_NEW
    (FK1, FK2, value)
SELECT cte.FK1, cte.FK2, cte.value
FROM cte
WHERE cte.[rn] = 1

INFORMACIÓN ADICIONAL

Ejecuté la siguiente consulta: en la parte externa, el código verifica si hay alguna duplicación; en el interior, hay una SELECCIÓN DISTINTA, igual que la Consulta 2. Los resultados son más de 1k filas, por lo que tengo algo incorrecto en las consultas.

SELECT D.FK1, D.FK2, COUNT(D.FK1) AS count
FROM (
    SELECT DISTINCT D.FK1, D.FK2, D.value
    FROM HDR AS H,
        DET AS D
    WHERE H.Date  >= CONVERT( datetime, '2018-02-01 00:00:00', 20 )
        and H.Date < CONVERT( datetime, '2018-02-03 00:00:00', 20 ) 
        and H.FK1 = D.FK1
    )D
GROUP BY D.FK1, D.FK2
HAVING 
    COUNT(*) > 1

Las preguntas son: ¿Por qué estoy insertando duplicados? ¿Hay otra forma eficiente para una gran base de datos (miles de millones de filas)?

0
NDA 13 sep. 2018 a las 12:57

3 respuestas

La mejor respuesta

Es probable que para algunas combinaciones de FK1 y FK2, haya más de un value diferente en DET. La siguiente consulta debería demostrar que

SELECT FK1, FK2, MIN(value), MAX(value), COUNT(DISTINCT value)
FROM DET
GROUP BY FK1, FK2
HAVING COUNT(DISTINCT value) > 1

Deberá incluir el valor en la clave compuesta en DET_NEW o determinar cómo desea seleccionar qué valor utilizar para cada clave, p. utilizando un MIN o MAX. La consulta que proporcionó @MoinulIslam también lo ayudará a seleccionar un valor único para cada clave. En esa consulta, solo está eligiendo la primera.

1
cf_en 13 sep. 2018 a las 10:56

Debido a que está fragmentando la inserción, lo distinto solo está actuando en la "porción actual" y sospecho que, por lo tanto, está insertando una fila con un valor que ya existe, de ahí la violación de la restricción PK. Realmente debe proporcionar detalles en su pregunta sobre cuál es la composición de la clave primaria, pero supongo que es una combinación de los 3 campos fk1, fk2 y valor.

Quizás en lugar de fragmentar por fecha, que presumiblemente no guarda relación con los valores incluidos en FK1, FK2 y el valor, puede ordenar su conjunto de datos "entrantes" por esos campos, asegurando que todos los posibles duplicados se mantengan en su "fragmento entrante", por lo que que lo distinto puede funcionar en eso.

Así que tomaría uno de los campos, lo que sea que le brinde el mejor rendimiento y uso de esta manera:

Create table #tt(
 fk1 fk1_datatype null
)

insert #tt(fk1)
select distinct fk1
from DET
order by fk1

<< while clause to get next value from #tt and read into @fk1>>

INSERT INTO DET_NEW
(FK1, FK2, value)
SELECT distinct FK1, FK2, value
FROM DET AS D
WHERE D.FK1 = @fk1

<< end of while clause removing @fk1 from #tt >>
0
blackrussian 13 sep. 2018 a las 10:16
INSERT INTO DET_NEW
    (FK1, FK2, value)   
SELECT  K.FK1, K.FK2, K.value
FROM
(SELECT D.FK1, D.FK2, D.value,
    ROW_NUMBER() OVER (PARTITION BY D.FK1, D.FK2 ORDER BY D.FK1, D.FK2) AS RN
FROM HDR AS H
    JOIN DET AS D ON H.FK1 = D.FK1
WHERE H.Date  >= CONVERT( datetime, '2015-01-01 00:00:00', 20 )
    and H.Date < CONVERT( datetime, '2016-01-01 00:00:00', 20 )) K
    WHERE K.RN=1
1
cf_en 14 sep. 2018 a las 10:15