Adam Tappis (12/30/2008)
Although I agree that there is merit in this technique (as I've used it myself on previous projects). I would say this "quick and dirty" attempt is like re-inventing the wheel and making it oval!
I prefer the comparison that I show you a hammer, and you say "but it's not a screwdriver!". I continue to find it useful from time to time, and will continue to use it - if you don't - well, don't use it!
This is nothing more than something I knocked up in an idle few minutes that I thought was worth sharing. I did google for something similar first, but didn't hit on the right combination of search terms. One thing I was trying to achieve was to keep things as simple as possible while getting to the intended result, so that the procedure itself would perhaps spark some ideas in people about what could be done in terms of using SQL to write SQL.
- place the scripts in source control
- perform simple data comparisson
- ease deployment (you'd be surprised how many DBA's refuse to or are not competent to do anything other than run SQL scripts)
This really goes way beyond the intention here. I guess you could use it to script some key coding table data, and source control that, but really that's not what it is for (personally, I would tend to use something like Red Gate SQL Data Compare for that). I use this for much more "ad hoc" tasks - when setting up a SQL Compare project or a SSIS/DTS job would be overkill.
- Using NVARCHAR(MAX) so as not to exceed the 4000/8000 character limit
I still have some SQL 2000 databases it needs to work on. No reason not to change it for SQL 2005 and above ...
- handling binary data correctly
Personally, just didn't feel the need - if it gets much beyond what can be verified visually, either in number of records, number of fields or the data types it needs to operate on, using any of the several other available solutions might be more appropriate. I guess it would be easy enough to do.
- why not generate a single insert instead of 1 per row e.g. INSERT ... SELECT ... UNION ALL SELECT ...
Certainly could do - but why? This is intended for a small number of records, so performance is not a particular issue. Mind you, t would mitigate considerably the string length limitations for tables with a lot of columns I guess. I tend not to use this where there are a lot of columns though - I would prefer using something like SQL Data Compare that gives me a more digestible preview of what is going to happen in those cases.
- why not make the insert re-runnable e.g. using a WHERE NOT EXISTS clause to only insert data that's not already there
Presumably based on ascertaining the key fields? Again, I was staying away from the additional complexity because I didn't particularly feel the need, and also with the limitations on string length brought about by the general approach, this could itself cause some problems.
- even better with SQL 2008 you could generate a MERGE query to INSERT, UPDATE or DELETE as appropriate
See SQL 2000 comment earlier.