Equivalent of SQL Server bulk insert for updates?

Question

I've made extensive use of bulk insert for onboarding and similar tasks. Unless I'm mistaken, one of the main reasons this runs rapidly is that much of the work is done locally on the server as opposed to sending up a large number of INSERT queries.

Now I'm looking at a similar issue for UPDATE. We are collecting changes to different fields in different tables from a file of the basic form TABLE/FIELD/PKEY=new value. I do not find anything similar to a BULK UPDATE, and looking at previous threads here it seems the solution is to upload ~= 2000 UPDATEs at a time.

I built a DB with 30 tables. Using BULK INSERT I can populate 1k rows in each in < 1 second. I then wrote some UPDATE code using a 2k row limit (so everything was a single batch per table in my DB) and that took 300 seconds. This is not unexpected, each UPDATE includes a WHERE so this is going to be more expensive.

Am I missing a higher performance solution?

I usually do a json of the changes and then join the main table with an OPENJSON call to do the update once — siggemannen
– siggemannen, Commented Oct 11, 2024 at 14:51
It was much faster for my case, but of course your mileage will vary :) — siggemannen
– siggemannen, Commented Oct 11, 2024 at 14:55
"Am I missing a higher performance solution?" We can't effectively answer that unless we see a sample of your table schema (including PKs, FKs, and indexes), the actual code you are running, and preferably the actual execution plan for the updates uploaded to Paste the Plan. — T N
– T N, Commented Oct 11, 2024 at 17:07
No there isn't. A common technique is to use a Table Valued Parameter, or a temp table with bulk-copy, both of which should be fast to insert. If they are indexed then the resulting joined update should be pretty fast. — Charlieface
– Charlieface, Commented Oct 12, 2024 at 20:28

David Browne - Microsoft · Accepted Answer · 2024-10-13 17:19:26Z

1

Am I missing a higher performance solution?

No there isn't. A common technique is to use a Table Valued Parameter, or a temp table with bulk-copy, both of which should be fast to insert. If they are indexed then the resulting joined update should be pretty fast.

You can also create a json of the changes and then join the main table with an OPENJSON call to do the update once.

answered Oct 13, 2024 at 17:19

community wiki

David Browne - Microsoft

Sign up to request clarification or add additional context in comments.

1 Comment

Maury Markowitz Over a year ago

Thanks! Just wanted to be sure I wasn't missing something. I think I'll go with the JSON solution as it would solve another problem at the same time.

Collectives™ on Stack Overflow

Equivalent of SQL Server bulk insert for updates?

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related