Мне нужно периодически обновлять очень большую таблицу, и SQLBulkCopy идеально подходит для этого, только у меня есть индекс с двумя столбцами, который предотвращает дублирование. Есть ли способ использовать SQLBulkCopy как «вставить или обновить, если существует»?
Если нет, то как это сделать наиболее эффективно? Опять же, я говорю о таблице с миллионами записей.
Спасибо
Я бы загрузил данные во временную промежуточную таблицу, а затем сделал бы upsert в итоговую таблицу. См. здесь для примера выполнения апсерта.
Я опубликовал пакет nuget (SqlBulkTools) для решения этой проблемы.
Вот пример кода, который позволяет выполнить массовое обновление.
var bulk = new BulkOperations();
var books = GetBooks();
using (TransactionScope trans = new TransactionScope())
{
using (SqlConnection conn = new SqlConnection(ConfigurationManager
.ConnectionStrings["SqlBulkToolsTest"].ConnectionString))
{
bulk.Setup<Book>()
.ForCollection(books)
.WithTable("Books")
.AddAllColumns()
.BulkInsertOrUpdate()
.MatchTargetOn(x => x.ISBN)
.Commit(conn);
}
trans.Complete();
}
Для очень больших таблиц есть опции для добавления блокировок таблиц и временного отключения некластеризованных индексов. Дополнительные примеры см. В документации SqlBulkTools.
Не за один шаг, но в SQL Server 2008 вы могли:
MERGE для обновления / вставки в вашу настоящую таблицуПодробнее об заявлении MERGE
Вместо создания новой временной таблицы, которая, кстати, занимает больше места и памяти.
Я создал триггер с INSTEAD OF INSERT и использую внутри оператора MERGE.
Но не забудьте добавить параметр SqlBulkCopyOptions.FireTriggers в SqlBulkCopy.
Это мои два цента.
Другой альтернативой было бы не использовать временную таблицу, а использовать хранимую процедуру с параметром, имеющим табличное значение. Передайте объекту данных объекту sp и выполните слияние там.
Получил подсказку от @Ivan. Для тех, кому может понадобиться, вот что я сделал.
create trigger yourschma.Tr_your_triger_name
on yourschma.yourtable
instead of INSERT
as
merge into yourschma.yourtable as target
using inserted as source
on (target.yourtableID = source.yourtableID)
when matched then
update
set target.ID = source.ID,
target.some_column = source.some_column,
target.Amount = source.Amount
when not matched by target then
insert (some_column, Amount)
values (source.some_column, source.Amount);
go
