When SQL Output Makes Me Cry

I'm a big proponent of the SQL OUTPUT clause introduced in SQL 2008. Having the ability to output the rows of a given DML statement has a lot of great uses. You can log DML actions without the need for triggers or costly features like CDC, you can capture data moving around inside a procedure, or debug a one-off script you're running. I did however just run into a situation with makes me want to cry.

Typically with the DML procedures I work with, we like to put all the data manipulation, massaging and preparation at the start, and then at the end persist all this data to the presentation tables. While I always realized why, it became especially apparent here. If it's not clear, the benefit here is that you have all your staging done in temporary objects and if something goes wrong, you can break out and not have to worry about anything live being affected. You also avoid convoluted schemes of transactions and try catch blocks. Also you know where to look for your DML statements modifying presentation tables. Now this doesn't always work, but I think for the most part, a small amount of effort can fit this mold. One big problem comes with debugging a procedure. You need to see data flow through the procedure in order to test individual parts and see where things are going wrong. If you have a presentation table DML action early in the procedure, if you want to run it in any environment but a development environment, you likely wont have the permissions to affect the tables (and if you do, you still probably don't want to). This is easy to get around by changing the

  insert into MyPresentationTable (ColumnA, ColumnB) select ColumnA, ColumnB from #TempTable   

to something like

 select ColumnA, ColumnB into #MyTempPresentationTable from #TempTable  

Now you have a temp object which holds the temporary data you need, and you can move on with debugging. However, with the advent of the OUTPUT clause, you can now make selections from things like Merge statements, output into statements, and so forth. The case I have now is that a DML action is performed way early in the procedure, via a merge statement, and the OUTPUT of that is then used to feed a temp table used in just about every subsequent statement in the procedure. In the procedure, the $action attribute is used in conjunction with deleted.* and inserted.*. Trying to re-create the contents of a merge statement is difficult enough to do by hand, but when the situational outputs of a merge statement are then used for everything else, running this in an environment where you can't just run it and fill up objects to debug is a nightmare. I'm a big fan of the OUTPUT statement, and actually a pretty big fan of the MERGE statement as well, but please, consider how it's being used. New features are wonderful, but they don't have the time tested trial and error of longer standing methods. You have to give more thought to how you use them because dealing with them and debugging them is going to raise issues which don't typically come up with more core features.