UPDATE using a case statement -- it looks like you only have two variations.

Is the IN clause even necessary? -- i.e., does part have to be in inmast, or can...

If you used a stored procedure to do the update, you could also put history logic in the same procedure that does the update.

Triggers are a common way to maintain...

It was a known problem with multiple displays: https://blog.danskingdom.com/tell-microsoft-to-fix-the-sql-server-management-studio-connect-to-server-dialog-position/

This link suggests  just hitting escape (or enter) & then moving SSMS around allows you to get dialog in a useable...

Which version of SQL Server Management Studio?

Not SQL version/edition specific -- it happened w/ the ADS 1.40 release: https://learn.microsoft.com/en-us/sql/azure-data-studio/release-notes-azure-data-studio?view=sql-server-ver16#november-2022

SELECT * FROM #tst
WHERE EXISTS (SELECT * FROM #tst dupe
              WHERE dupe.ID = #tst.ID...

When did you move to SQL Server 2019?

Are you getting that error with any/all parameters, or only w/ specific cases? If only some cases, what parameters result in this error?

Did...

How many relevant unique tag_ids are there? Are they relatively static, or are new ones inserted continually?

Can't you insert those into a table on the linked server (or just hardcode...

Take a look at https://techcommunity.microsoft.com/t5/sql-server-blog/sql-server-2016-minimal-logging-and-impact-of-the-batchsize-in/ba-p/385537. You may be able to take advantage of minimal logging to bulk load the data.

BCP or bulk insert are an option for loading a table...

Use a sequence. Don't waste resources querying max(id).

Is there really a need to partition the detail ids by tenant? If so, then use a separate sequence for each tenant w/...

You didn't indicate schema in your pseudocode. Did you use schema name in your query? (Recommend you always do to avoid ambiguity)

Any tables, views, or synonyms w/ the same name...

And, of course, look at actual execution plan to see if you have index needed to support the where clause(s) for the updates. Without that, it may be having to...

Are you seeing blocking/locking?

What transaction isolation level does the database use? the connections?

It's likely that batching the updates with far fewer connections will be much faster than issuing 5 million...

For a very small set I'd probably use NOT IN instead of anding multiple exclusions --

WHERE airports.airport_city NOT IN ('New York City','Los Angeles')

For more than a few,...

sp_clean_db_free_space

Is ghost cleanup turned off (i.e., do you have trace flag 661 enabled?