34

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

d219
  • 2,707
  • 5
  • 31
  • 36
Sol
  • 365
  • 1
  • 3
  • 6

6 Answers6

21

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.

Dongdong
  • 2,208
  • 19
  • 28
Greg R Taylor
  • 3,470
  • 1
  • 25
  • 19
  • 2
    I have to say, this library is chocked full of goodness. I had to write a windows service that did some ETL work and the database interaction was not enough to justify even a MicroORM and this library SqlBulkTools was perfect. Thank you!. Nice work! – Hallmanac Apr 19 '17 at 23:21
  • 3
    The documentation link is dead (404). – Gord Thompson May 11 '17 at 20:17
  • 6
    SqlBulkTools was sold to ZZZ Projects. – Greg R Taylor May 12 '17 at 01:54
  • It's 2020 and I am using this package. The performance is great and it's exactly what I needed for bulk insert or update! – Rob L Apr 24 '20 at 20:37
  • Also working great, any idea on using a CancellationToken to bail out partial operation (since these will likely be very long running)? – morleyc Jul 11 '20 at 16:46
14

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.

Robert Harvey
  • 178,213
  • 47
  • 333
  • 501
btilly
  • 43,296
  • 3
  • 59
  • 88
12

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

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
11

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.

Ivan Paniagua
  • 312
  • 2
  • 10
2

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.

jb5253
  • 21
  • 2
2

Got a hint from @Ivan. For those who might need, here's what I did. 

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
  • 606
  • 7
  • 11