Hugo Kornelis (8/9/2010)
It's an interesting technique - but not one I would recommend!The simple version has this caveat: it works "as long as they share the same primary key and the shared columns have the same names" (quote from the article). Verifying that is what takes time when preparing a data merge (be it a one-time operation or an ETL job that is to be run repeatedly). Building the actual statement is a matter of seconds, especially if you use SSMS' drag&drop techniques to copy the column list from the object explorer. The technique shown in this article doe indeed save you a couple of seconds and a few keystrokes, but at the price of having to use dynamic SQL (which means you have to run under a user account with elavated rights, and you incrue the risk of injection attacks). And worse - it invites laziness. There might be situations where the same name has been used for essentially different colum; I'll exclude those when working manuallly, but they'll be included by this procedure. Or there might be some subtle difference in how data is represented, making some conversion required.
You make some valid criticisms, and when dealing with a relatively small number of tables, I agree with you. Given just a small handful of tables, it is much better to hand craft the statements and ensure they are correct and perform exactly what you want and to avoid the dynamic SQL this entails..
I first used this technique when dealing with moving over a dozen slight variations of a table into one consolidated analysis table, and moreover in that case for certain business reasons I expected many more slight variations of that table to be generated and need to be merged in as well. In that case, generating it dynamically was vastly easier to write and more maintainable than statically creating the insert code for every one of the variations.
The second time I used it was when I had around 30 tables all of which had one of five slight variations of a schema that all needed to be merged into an analysis table in a one-time ETL job.
As to whether you should use it in production, I would certainly approach that with caution, but I try to avoid any dynamic SQL in production where that is possible. As mentioned, I have used this in moving from production servers to analysis/reporting servers. I have also used it for ETL for data that was going into production, but that always had a staging location involved for final verification before moving it to production
---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/