Как предотвратить вставку повторяющихся записей с помощью SqlBulkCopy при отсутствии первичного ключа

Я загружал данные в промежуточную таблицу, а затем обрабатывал дубликаты при копировании в итоговую таблицу.

Например, вы можете создать (неуникальный) индекс в промежуточной таблице для работы с «ключом»

Учитывая, что вы используете SQL 2008, у вас есть два варианта простого решения проблемы без значительного изменения приложения (если вообще).

Первое возможное решение - создать вторую таблицу, подобную первой, но с суррогатным идентификационным ключом и ограничением уникальности, добавленным с помощью опции ignore_dup_key, которая сделает всю тяжелую работу по удалению дубликатов за вас.

Вот пример, который вы можете запустить в SSMS, чтобы увидеть, что происходит:

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

В качестве альтернативы вы также можете удалить дубликаты на месте без второй таблицы, но производительность может быть слишком низкой для ваших нужд. Вот код этого примера, который также можно запустить в 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

Я бы скопировал массово во временную таблицу, а затем вставил данные из нее в фактическую целевую таблицу. Таким образом, вы можете использовать SQL для проверки и обработки дубликатов.

Почему бы просто не использовать вместо первичного ключа создать индекс и установить

Ignore Duplicate Keys: YES

Это предотвратит появление ошибки при любом дублировании ключа и не создаваться (поскольку он уже существует).

Я использую этот метод для вставки около 120 000 строк в день и работает безупречно.

Какой объем данных? У вас есть 2 варианта, которые я вижу:

1: отфильтруйте его в источнике, реализовав свой собственный IDataReader и используя некоторый хеш для данных, и просто пропустив любые дубликаты, чтобы они никогда не попадали в TDS.

2: отфильтровать в БД; на самом простом уровне, я полагаю, у вас может быть несколько этапов импорта - сырые, не подвергнутые анализу данные - а затем скопировать DISTINCT данные в ваши фактические таблицы, возможно, используя промежуточную таблицу, если хотите. Вы можете использовать CHECKSUM для некоторых из них, но это зависит от обстоятельств.

И почини эту таблицу. Ни одна таблица никогда не должна быть без уникального индекса, желательно в виде ПК. Даже если вы добавляете суррогатный ключ из-за отсутствия естественного ключа, вам необходимо иметь возможность конкретно идентифицировать конкретную запись. Иначе как избавиться от уже имеющихся дубликатов?

Я думаю, это намного чище.

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 );

}

Таким образом, потребуется только одна таблица базы данных и можно будет сохранить бизнес-логику в коде.