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

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.

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.

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.

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