I’m not a fan of most CRUD generators. The formatting doesn’t match my style, and I usually spend about as much time modifying the generated code as I would spend just writing it from scratch. But there’s been times when I’ve considered using CRUD generators, mainly when I’m writing updates on wide tables. If you’ve never written an update for a table with many columns, it’s not sexy. You’re wasting valuable time on a tedious task that you could instead spend reading SQL Server 2008 Internals or chewing the cud with the SQL Twitterati.
Fortunately, Dave Carlile shared another tip with me that helps with this and has made it’s way into my little bag of tricks.
Let’s assume you having the following outline:
Update sales Set ['insert really long column list'] From Sales.vStoreWithDemographics As sales Join myTempTable As mtt On sales.someColumn = mtt.someColumn; |
You could use the following code to generate a list of columns for you:
Select name + ' = sales.' + name + ',' From sys.columns Where object_id = object_id('Sales.vStoreWithDemographics') Order by column_id; |
Just replace [Sales.vStoreWithDemographics] with a table of your choice, and replace “sales.” with the appropriate alias.This will return a list of nicely formatted columns for you. Best of all, no potential for column typos! Just don’t forget to remove the very last comma, otherwise you’ll get a syntax error.
CustomerID = sales.CustomerID, Name = sales.Name, ContactType = sales.ContactType, (etc.) |
I know, nothing earth shattering, but definitely one of those “huh, why didn’t I think of that?” moments. So, thanks, Dave! 🙂
Source: http://sqlfool.com/2009/03/generate-columns-for-update-statements
