c# - example - Cómo evitar que se inserten registros duplicados con SqlBulkCopy cuando no hay una clave principal

sqlbulkcopy datatable c#

Question

Recibo un archivo XML diario que contiene miles de registros, cada uno de los cuales es una transacción comercial que necesito almacenar en una base de datos interna para uso en informes y facturación. Tenía la impresión de que el archivo de cada día contenía solo registros únicos, pero he descubierto que mi definición de único no es exactamente la misma que la del proveedor.

La aplicación actual que importa estos datos es una aplicación de consola C # .Net 3.5, lo hace utilizando SqlBulkCopy en una tabla de base de datos MS SQL Server 2008 donde las columnas coinciden exactamente con la estructura de los registros XML. Cada registro tiene un poco más de 100 campos, y no hay una clave natural en los datos, o más bien los campos que puedo encontrar tienen sentido, ya que una clave compuesta también tiene que permitir nulos. Actualmente la tabla tiene varios índices, pero ninguna clave primaria.

Básicamente, toda la fila debe ser única. Si un campo es diferente, es lo suficientemente válido como para insertarlo. Observé cómo crear un hash MD5 de toda la fila, insertarlo en la base de datos y usar una restricción para evitar que SqlBulkCopy inserte la fila, pero no veo cómo introducir el Hash MD5 en la operación de BulkCopy y no estoy asegúrese de que la operación en su totalidad falle y se deshaga si algún registro falla, o si continúa.

El archivo contiene una gran cantidad de registros, yendo fila por fila en el XML, consultando la base de datos en busca de un registro que coincida con todos los campos, y luego decidir insertar es realmente la única forma en que puedo ver poder hacer esto. Esperaba no tener que volver a escribir la aplicación por completo, y la operación de copia masiva es mucho más rápida.

¿Alguien sabe de una manera de usar SqlBulkCopy mientras previene filas duplicadas, sin una clave principal? ¿O alguna sugerencia para una forma diferente de hacer esto?

Answer 1

¿Cuál es el volumen de datos? Tienes 2 opciones que puedo ver:

1: filtrarlo en la fuente, implementando su propio IDataReader y usando algo de hash sobre los datos, y simplemente omitiendo cualquier duplicado para que nunca se transfieran al TDS.

2: filtrarlo en el DB; en el nivel más simple, supongo que podría tener varias etapas de importación (los datos sin procesar y sin autorización) y luego copiar los datos de DISTINCT en sus tablas reales , tal vez utilizando una tabla intermedia si así lo desea. Es posible que desee utilizar CHECKSUM para algo de esto, pero depende.

Answer 2

¿Por qué no utilizar simplemente, en lugar de una clave principal , crear un índice y establecer

Ignore Duplicate Keys: YES

Esto evitará que cualquier clave duplicada emita un error y no se creará (ya existe).

Utilizo este método para insertar alrededor de 120.000 filas por día y funciona perfectamente.

Answer 3

Creo que esto es mucho más limpio.

var dtcolumns = new string[] { "Col1", "Col2", "Col3"};

var dtDistinct = dt.DefaultView.ToTable(true, dtcolumns);

using (SqlConnection cn = new SqlConnection(cn) 
{
                copy.ColumnMappings.Add(0, 0);
                copy.ColumnMappings.Add(1, 1);
                copy.ColumnMappings.Add(2, 2);
                copy.DestinationTableName = "TableNameToMapTo";
                copy.WriteToServer(dtDistinct );

}

De esta manera solo necesita una tabla de base de datos y puede mantener el código de Bussiness Logic.

Answer 4

Dado que está utilizando SQL 2008, tiene dos opciones para resolver el problema fácilmente sin tener que cambiar mucho su aplicación (si es que lo hace).

La primera solución posible es crear una segunda tabla como la primera, pero con una clave de identidad sustituta y una restricción de singularidad agregada con la opción ignore_dup_key que hará todo el trabajo de eliminar los duplicados por usted.

Aquí hay un ejemplo que puede ejecutar en SSMS para ver qué está pasando:

if object_id( ''tempdb..#test1'' ) is not null drop table #test1;
if object_id( ''tempdb..#test2'' ) is not null drop table #test2;
go


-- example heap table with duplicate record

create table #test1
(
     col1 int
    ,col2 varchar(50)
    ,col3 char(3)
);
insert #test1( col1, col2, col3 )
values
     ( 250, ''Joe''''s IT Consulting and Bait Shop'', null )
    ,( 120, ''Mary''''s Dry Cleaning and Taxidermy'', ''ACK'' )
    ,( 250, ''Joe''''s IT Consulting and Bait Shop'', null )    -- dup record
    ,( 666, ''The Honest Politician'', ''LIE'' )
    ,( 100, ''My Invisible Friend'', ''WHO'' )
;
go


-- secondary table for removing duplicates

create table #test2
(
     sk int not null identity primary key
    ,col1 int
    ,col2 varchar(50)
    ,col3 char(3)

    -- add a uniqueness constraint to filter dups
    ,constraint UQ_test2 unique ( col1, col2, col3 ) with ( ignore_dup_key = on )
);
go


-- insert all records from original table
-- this should generate a warning if duplicate records were ignored

insert #test2( col1, col2, col3 )
select col1, col2, col3
from #test1;
go

Alternativamente, también puede eliminar los duplicados en el lugar sin una segunda tabla, pero el rendimiento puede ser demasiado lento para sus necesidades. Aquí está el código para ese ejemplo, también ejecutable en SSMS:

if object_id( ''tempdb..#test1'' ) is not null drop table #test1;
go


-- example heap table with duplicate record

create table #test1
(
     col1 int
    ,col2 varchar(50)
    ,col3 char(3)
);
insert #test1( col1, col2, col3 )
values
     ( 250, ''Joe''''s IT Consulting and Bait Shop'', null )
    ,( 120, ''Mary''''s Dry Cleaning and Taxidermy'', ''ACK'' )
    ,( 250, ''Joe''''s IT Consulting and Bait Shop'', null )    -- dup record
    ,( 666, ''The Honest Politician'', ''LIE'' )
    ,( 100, ''My Invisible Friend'', ''WHO'' )
;
go


-- add temporary PK and index

alter table #test1 add sk int not null identity constraint PK_test1 primary key clustered;
create index IX_test1 on #test1( col1, col2, col3 );
go


-- note: rebuilding the indexes may or may not provide a performance benefit

alter index PK_test1 on #test1 rebuild;
alter index IX_test1 on #test1 rebuild;
go


-- remove duplicates

with ranks as
(
    select
         sk
        ,ordinal = row_number() over 
         ( 
            -- put all the columns composing uniqueness into the partition
            partition by col1, col2, col3
            order by sk
         )
    from #test1
)
delete 
from ranks
where ordinal > 1;
go


-- remove added columns

drop index IX_test1 on #test1;
alter table #test1 drop constraint PK_test1;
alter table #test1 drop column sk;
go

Answer 5

Subiría los datos a una tabla de preparación y luego trataré con duplicados después de copiarlos en la tabla final.

Por ejemplo, puede crear un índice (no único) en la tabla de preparación para tratar la "clave"

Answer 6

Y arregla esa mesa. Ninguna tabla debería estar sin un índice único, preferiblemente como un PK. Incluso si agrega una clave sustituta porque no hay una clave natural, debe poder identificar específicamente un registro en particular. De lo contrario, ¿cómo te desharás de los duplicados que ya tienes?

Answer 7

Yo haría una copia masiva en una tabla temporal y luego insertaría los datos en la tabla de destino real. De esta manera, puede usar SQL para verificar y manejar duplicados.