有没有一种方法可以在 SQLBulkCopy 中实现“如果存在则更新，否则插入”的操作？

Question

我需要定期更新一个非常大的表，SQLBulkCopy非常适合这个任务，只是我有一个由两列组成的索引，防止重复。是否有一种方法可以将SQLBulkCopy用作“插入或更新（如果已存在）”？

如果不行，那么最有效的方法是什么？再次强调，我说的是一个包含数百万条记录的表。

谢谢

- Sol

Answer 1

我发布了一个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文档。

- Greg R Taylor

---

我必须说，这个库充满了好处。我不得不编写一个Windows服务来执行一些ETL工作，而数据库交互并不足以证明需要使用MicroORM，而这个SqlBulkTools库则非常完美。谢谢！干得好！ - Hallmanac

文档链接已失效（404）。 - Gord Thompson

SqlBulkTools已被出售给ZZZ Projects。 - Greg R Taylor

现在是2020年，我正在使用这个软件包。它的性能非常出色，正好满足了我进行批量插入或更新所需的要求！ - Rob L

也很好用，有没有想过使用CancellationToken来取消部分操作（因为这些操作可能会非常耗时）？ - morleyc

Answer 2

我会将数据批量加载到临时分段表中，然后执行 upsert 操作到最终表中。请参考这里，了解如何执行 upsert 操作的示例。

- btilly

---

我对SSIS不太熟悉，它究竟如何帮助完成这个任务？ - Sol

这是非常昂贵的操作。需要创建一个临时表，然后再进行合并。 - Alex M

Answer 3

不是一步完成的，但在SQL Server 2008中，您可以：

• 批量加载到临时表中
• 应用MERGE语句以更新/插入到您的实际表中

了解更多有关MERGE语句的信息。

- marc_s

Answer 4

我使用了INSTEAD OF INSERT触发器，而不是创建一个新的临时表，这样会消耗更多的空间和内存。

在MERGE语句中使用了该触发器。

但不要忘记在SqlBulkCopy中添加参数SqlBulkCopyOptions.FireTriggers。

这是我的个人经验。

- Ivan Paniagua

Answer 5

另一种选择是不使用临时表，而是使用带有表值参数的存储过程。将 datatable 传递给存储过程，在那里进行合并操作。

- jb5253

Answer 6

我从@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

- Shengfeng Li