I rather drastically cut down the fiddle, but I think the intent shines through.
[Def] is a
[DefaultClassification] table, and
[Cls] is a
[Classification] table that has some old records that need to be added in. The DefaultClassification table will be used in the future to spawn new groups of records into yet another table, and we're "unbreaking" an existing bit of logic/data with this process.
I did my best to distill the entire thing down to the barest essentials, but I've got another similar process that runs right after this one, so I'm looking to learn best practices and why this query is broken.
When I wrote this I intended it to run in a 2008+ environment, scripted or manually in SSMS. I don't know that it won't be scripted, but it might. Right now it's entirely run by hand. The rollback and select at the top above the print messages at the end are because it doesn't work right, so this lets me validate it before committing. I would prefer a valid script that I don't have to muck with, however
The specific issue I have is on line 62 of that revision one link http://sqlfiddle.com/#!3/ef71e/1 and it looks like this:
RIGHT OUTER JOIN [Def] tcd
if I flip that RIGHT to a LEFT it does the insert but doesn't detect duplicates (to prevent insertion) and if I do the RIGHT it detects duplicates but doesn't handle insertions.
What did I break? And Why?
And I'm told the use of the
@@ERROR was bad juju, but I don't know why, so bonus points (a 100 rep bounty) for that explanation too.