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

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

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.

I would bulk load data into a temporary staging table, then do an upsert into the final table. See here for an example of doing an upsert.

Not in one step, but in SQL Server 2008, you could:

• bulk load into staging table
• apply a MERGE statement to update/insert into your real table

Read more about the MERGE statement

Instead of create a new temporary table, which BTW consume more space and memory.

I created a Trigger with INSTEAD OF INSERT and use inside MERGE statement.

But don't forget add the parameter SqlBulkCopyOptions.FireTriggers in the SqlBulkCopy.

This is my two cents.

Another alternative would be to not use a temporary table but use a stored procedure with a table valued parameter. Pass a datatable to the sp and do the merge there.