Any way to SQLBulkCopy "insert or update if exists"?

Question

I need to update a very large table periodically and SQLBulkCopy is perfect for that, only that I have a 2-columns index that prevents duplicates. Is there a way to use SQLBulkCopy as "insert or update if exists"?

If not, what is the most efficient way of doing so? Again, I am talking about a table with millions of records.

Thank you

Answer 1

I published a nuget package (SqlBulkTools) to solve this problem.

Here's a code example that would achieve a bulk upsert.

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

For very large tables, there are options to add table locks and temporarily disable non-clustered indexes. See SqlBulkTools Documentation for more examples.