Generate Columns for Update Statements

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

0saves
If you enjoyed this post, please consider leaving a comment or subscribing to the RSS feed to have future articles delivered to your feed reader.
Tagged . Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>