T-SQL: Generate set based update statements easily!

CTRL+H (Replace) and use a Regex:

[,]\ +[a-z]+(\(+[0-9]+[,]*[0-9]+\))*[,]\>

Let me demonstrate, here’s my dummy table:

Dummy-Update-Table
Generate the standard UPDATE-scripts via SSMS:

SSMS-Generate-Update-Statement
And here’s the outcome:

SSMS-Generated-Update-Statement-result
Now hit CTRL+H to open the ‘find and replace’ window.
Paste in the Regex-statement and click on the checkbox for ‘Use‘ and select ‘Regular expressions

SSMS-Find-and-Replace-Regular-Expressions
Click on ‘Replace all‘ and have a look at the outcome:

SSMS-Generated-Update-Statement-After-RegEx
Almost done…click on the checkbox for ‘Use‘ again and deselect ‘Regular expressions‘ (disable). Now hit CTRL+H again and now replace the ‘<‘-character with ‘new.’:

SSMS-Find-and-Replace-New-Alias
The UPDATE-statement should now look like:

SSMS-Generated-Update-Statement-With-Alias
Final step is to add the FROM and WHERE-clauses as shown below:

SSMS-Generated-Update-Statement-Final-Result

You’re done! How easy huh?








Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s