Merge scalability for Insert/Deletes on Many2Many join table
I've been wracking my brain for several days trying to find a decent stored procedure way of taking a list of TagId integers from an input form for the current BuildingId, and checking against the join table to delete records for that BuildingId that aren't in the new TagId list, and inserting TagId records that aren't in the table. No other actual data is getting updated, just inserts of (BldgId, TagId) and deletes where the record isn't in the passed list/table.
I've finally stumbled upon the MERGE command, and have a pretty slick stored procedure now that appears to be working just great, but because the SOURCE table in the merge is a union of the passed (BldgId,TagId) values in a table with a SELECT BldgId,TagId from BldgTags WHERE BldgId<>@currentBldgId, I'm a bit worried how this might scale once I start getting hundreds or thousands of records in the table.
I've read the bugs list on the merge command, and I don't see anything drastic that should affect how I'm using it, as there literally isn't an update section of my command, just inserts on possibly 1-6 records and deletes of 1-6 records at most during any one call. Am I being naive here, or should the MERGE be fairly safe to use under these circumstances?
Also, second part, I'm currently wrapping the entire MERGE statement in a transaction. Not really sure if it's necessary or not, but not thinking it would hurt anything. When I was testing, it was helpful to wrap in transaction just so I could rollback and run other tests to make sure things worked as planned without permanently changing table rows.