Skip-más de/ignore filas duplicadas en insertar

Tengo las siguientes tablas:

DataValue

DateStamp    ItemId   Value
----------   ------   -----
2012-05-22   1        6541
2012-05-22   2        12321
2012-05-21   3        32

tmp_holding_DataValue

DateStamp    ItemId   Value
----------   ------   -----
2012-05-22   1        6541
2012-05-22   4        87
2012-05-21   5        234

DateStamp y ItemId son las columnas de clave principal.

Estoy haciendo un insert que se ejecuta periódicamente a lo largo del día (en un procedimiento almacenado):

insert into DataValue(DateStamp, ItemId, Value)
select DateStamp, ItemId, Value from tmp_holding_DataValue;

Este mueve los datos de la tabla de explotación (tmp_holding_DataValue) a través de las principales de la tabla de datos (DataValue). La celebración de la tabla es truncada a continuación.

El problema es que, como en el ejemplo, la celebración de la tabla puede contener elementos que ya existen en la tabla principal. Dado que la clave no va a permitir valores duplicados, el procedimiento va a fallar.

Una opción sería poner una cláusula where en la inserción de proc, pero los principales datos de la tabla tiene 10 millones de filas, y esto podría llevar mucho tiempo.

¿Hay alguna otra manera de obtener el procedimiento de saltar-más de/ignorar los duplicados como se intenta insertar?

  • ¿Qué pasa si el Value columna en la tabla de explotación es diferente, por ejemplo, para la primera fila es 3253 en lugar de 6541? Es que sigue siendo un duplicado? Si no, es algo que desea actualizar (por ejemplo, añadir 6541 + 3253 en la tabla de origen) o simplemente reemplazar?
  • El valor de la columna no importa, si es diferente, es ignorado, lo que está en el DataValue ya para que datestamp debe dejarse como está
  • También es muy útil para etiquetar a tu pregunta con el mínimo de la versión de SQL Server, se necesita apoyo. Yo no ofrecen un MERGE solución porque al principio yo no tenía absolutamente ninguna idea de lo que la versión que estaba utilizando.
  • Sí, yo debería haber dicho que estoy utilizando 2008 (poco antes de mudarse a 2012). Te gustaría ir con mezcla sobre el donde en ese caso?
  • No estoy seguro, creo que la MERGE sintaxis de enormes proporciones, y estoy indeciso recomendar en general. No estoy seguro de si todos los de la COMBINACIÓN de errores han sido corregidos (ver lista de referencia por Alex K en su respuesta para este 2012 pregunta). Si su objetivo clave es el rendimiento, entonces le toca a usted para ponerlos a prueba y asegúrese de que (a) a hacer lo correcto y (b) usted elegir el que funciona mejor en su entorno. No podemos predecir las respuestas a esas preguntas…
InformationsquelleAutor finoutlook | 2012-05-22

4 Kommentare

  1. 28
    INSERT dbo.DataValue(DateStamp, ItemId, Value)
    SELECT DateStamp, ItemId, Value 
    FROM dbo.tmp_holding_DataValue AS t
    WHERE NOT EXISTS (SELECT 1 FROM dbo.DataValue AS d
    WHERE DateStamp = t.DateStamp
    AND ItemId = t.ItemId);
    • Esto iba a funcionar, pero me preguntaba si había algo más rápido que en caso que el valor de datos de la tabla, finalmente, termina con 100 millones de filas
    • Si la clave principal es agrupada y la celebración de la tabla tiene un equivalente índice, a continuación, que no debería ser un problema (o al menos no más de un problema que cualquier otra solución que busca duplicados). ¿La tabla de explotación vez tiene datos «antiguos», o siempre te anexando lugar bastante nuevo los datos? Usted puede agregar donde cláusulas que limitan la fecha a algo razonable, como hace dos días, y si DateStamp es el líder en la columna de la clave primaria esto debería ayudar un poco. Pero sólo si usted siempre tiene nuevos datos en la tabla de explotación.
    • Gracias me fue con esta solución
    • Si I/O se convierte en un problema o desea limitar la contención en la tabla, se puede ejecutar en lotes mediante el uso de algo como SELECT TOP 10000. Puesto que en cada iteración se insertar registros que descalifican a sí mismos en posteriores ejecuciones, esto le permitirá limitar la cantidad de un efecto que tienes en tu servidor. Usted no tiene que molestarse con un ORDER BY si tu objetivo es tener todo lo que se trasladó desde entonces no te importa si la selección es determinista más.
  2. 20

    Usted puede asignar el PK como hacer caso omiso de Clave Duplicada = Sí. A continuación, se acaba de dar una advertencia de clave duplicada ignorado y continuar. No estoy adivinando. He probado esto.

    Lo que he encontrado es que no puedo hacer este es de los SMS. Tiene que quitar y volver a crear el índice a través de la secuencia de comandos. Pero usted puede hacer clic derecho en el índice, seleccione quitar y volver a crear y, a continuación, cambie simplemente Ignorar Clave Duplicada = Sí. Para mí de los SMS no se muestra inmediatamente el cambio.

    IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[PKallowDup]') AND name = N'PK_PKallowDup')
    ALTER TABLE [dbo].[PKallowDup] DROP CONSTRAINT [PK_PKallowDup]
    GO
    
    USE [test]
    GO
    
    /****** Object:  Index [PK_PKallowDup]    Script Date: 05/22/2012 10:23:13 ******/
    ALTER TABLE [dbo].[PKallowDup] ADD  CONSTRAINT [PK_PKallowDup] PRIMARY KEY CLUSTERED 
    (
        [PK] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = ON, IGNORE_DUP_KEY = ON, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    GO

    O creo que se podría utilizar una combinación externa

    INSERT dbo.DataValue(DateStamp, ItemId, Value)
    SELECT t.DateStamp, t.ItemId, t.Value 
      FROM dbo.tmp_holding_DataValue AS t 
      left join dbo.DataValue AS d
        on d.DateStamp = t.DateStamp
       AND d.ItemId = t.ItemId
     WHERE d.DateStamp is null 
       and d.ItemId    in null
    • Yo vi esta sugerido en otra parte, pero quería mantener la clave principal como era. Su crítica no hay duplicados en la final DataValue tabla.
    • ¿Por qué es esto difícil? WITH (IGNORE_DUP_KEY = ON); También @finoutlook ¿pruebe esta opción en una tabla simple? Es todavía una clave principal, y los duplicados todavía no están permitidos. El IGNORE_DUP_KEY configuración simplemente controla la forma en que SQL Server trata de infracciones de clave (con una excepción o con un simple mensaje de estado que dice Duplicate key was ignored.).
    • Todavía hay un PK y es forzada. La diferencia es que un PK violación es sólo una advertencia y continúa la inserción de filas cuando se Ignore Clave Duplicada = Sí.
    • ALTER INDEX [PK_PKallowDup] EN [dbo].[PKallowDup] RECONSTRUIR CON (IGNORE_DUP_KEY = ON); No para mí. Es que la sintaxis correcta? He dicho que he probado. Puedo crear una tabla con la condición o me puede caer y crear en una tabla vacía. No creo que el OP quería eliminar de sus filas de la tabla.
    • Usted no puede hacer esto con ALTER INDEX, pero sin duda se puede quitar y volver a crear la restricción, sin necesidad de «eliminar de sus filas de la tabla.» Mientras que esto puede ser costoso, es un costo por única vez como contraposición a pagar para que la comprobación de duplicados en cada inserción. En otras palabras me gusta tu respuesta, yo no creo que sea tan complicado como su respuesta implica.
    • OK, estás en lo correcto. Una gota y recrear alterarán el índice. Los SMS no reflejar de forma inmediata el cambio, incluso con una actualización, pero el cambio suceda. Si dejo SMS y abrirlo luego veo el cambio.
    • Para referencia en el futuro si la PK es la clave clúster se puede hacer con ALTER TABLE dbo.PKallowDup RECONSTRUIR CON (IGNORE_DUP_KEY = ON). Es extraño que no permite especificar el PK nombre, pero bueno 🙂
    • Sólo se quedó en la misma situación y su respuesta obras…esto debe ser aceptado respuesta, IMO

  3. 17

    En SQL Server 2008+:

    MERGE
    INTO    dataValue dv
    USING   tmp_holding_DataValue t
    ON      t.dateStamp = dv.dateStamp
            AND t.itemId = dv.itemId
    WHEN NOT MATCHED THEN
    INSERT  (dateStamp, itemId, value)
    VALUES  (dateStamp, itemId, value)
    /*
    WHEN MATCHED THEN
    UPDATE
            value = t.value
    */
    -- Uncomment above to rewrite duplicates rather than ignore them
    • Me hizo pensar en el uso de una combinación, pero con DataValue ser 10m+ filas, y tmp_holding_DataValue estar alrededor de 2m filas, pensé que iba a tomar un tiempo largo, ya que sería la comprobación de todos los datos de vuelta al principio de los tiempos en que la tabla de…
    • en otras palabras, se ha optimizado de forma prematura? Sólo probarlo.
    • Yo siempre preparados para lo peor y esperar lo mejor..! Te voy a dar un tiro
  4. 1

    Me encontré con un requisito similar que terminó tirando el mismo error de clave duplicada y, a continuación, la idea era seleccionar varias columnas que son distintas (Primaria), mientras que devolver también otras columnas, verificación:

    INSERT INTO DataValue(DateStamp, ItemId, Value)
    SELECT DISTINCT DateStamp, ItemId, MAX(Value) AS Value
    FROM tmp_holding_DataValue
    GROUP BY DateStamp, ItemId

    De hecho, el objetivo podría lograrse sin necesidad de Distinta así ya que la función de agregado MAX va a elegir a un solo valor.

Kommentieren Sie den Artikel

Bitte geben Sie Ihren Kommentar ein!
Bitte geben Sie hier Ihren Namen ein

Pruebas en línea