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.

Answer 2

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.