Thanks for the reply Kevin.
"Buggy" and "issues" are vague terms, but I get the picture. Also, I'm OK just taking your word for it on the triggers! My goal isn't to defend MERGE, I'm interested in your (or the Community's) recommendation for a set-based copy forward technique.
Not sure how standard that term is, so here's a typical scenario illustrating what I mean by "copy forward":
A Parent table has an IDENTITY int as a primary key (PK) and one or more child tables have foreign keys (FK) referencing it. A block of new parent records needs to be created with duplicate data except for some attribute (calendar year, for example) which is loaded with a new value. All of the related child records also need be duplicated except, of course, their FKs which need to match the new parent record PKs. The challenge is to perform a block insert on the Parent in a way that captures and ties new PKs to original PKs and also has acceptable concurrency.
IMHO, the best solution is to simply add a "CopiedFromID" column to the parent and insert the old PK into it when copying forward. The original child records can then be selected using CopiedFromID and their new FK loaded from the new Parent ID.
We often don't have the luxury of adding a new column though, so assuming adding CopiedFromID is not an option, what set-based approach can be taken to map old parent PKs to the newly inserted ones?
One set-based approach I've seen does the following:
opens a table lock on the parent table,
copies parent table IDs into a temp table which creates new ids in the temp table (using IDENT_CURRENT(ParentTable) for a seed),
Sets IDENTITY_INSERT ON,
Inserts new Parent records with temp table's new ID using join on temp table's old ID.
Turns IDENTITY_INSERT OFF,
Copies forward child table records using temp table,
Releases table lock upon completion.
This locks the parent table for the duration of the inserts which hurts concurrency. Plus we don't need to force new IDs. We just need to map the old IDs to what ever new ones SQL Server creates. If we use the OUTPUT clause of an INSERT, we can capture new IDs, but that's worthless because we don't have a map to the old IDs. That's what's so "magical" about using MERGE with an OUTPUT clause. It gives us the old-to-new mapping in the same statement that does the insert!
Here's what that approach does:
Merge to insert parent records and load old IDs mapped to new IDs into a temp table
Copies forward child table records using temp table
Is there an approach as elegant as this that doesn't use the "buggy" MERGE with "issues"?
Heck, if MERGE's only problem is that it chokes on insert triggers, it might still make sense to disable them and replicate their behaviour in the copy forward procedure, even though that would mean using TABLOCK to prevent other processes from inserting while the trigger is disabled.
I'm not trying to be argumentative. It just feels like I'm missing something obvious (besides adding that CopiedFromID column, 😉 ) and I'm interested in learning if others have run into this business need and if they've solved it without RBAR, IDENTITY_INSERT, or TABLOCK.