I work for Redgate and write about products. I’ve got a series of SQL Prompt posts here on little things I like. SQL Prompt might be my favorite tool. SQL Prompt will be yours as well if you give it a try.
We had a customer post a question today on how they can built an update statement with a pattern. Specifically, they said that the code often looks like:
UPDATE dbo.Contacts SET c.Salutation = @Salutation , c.FirstName = @FirstName , c.MiddleName = @MiddleName , c.LastName = @LastName , c.Suffix = @Suffix WHERE ContactID = @contactid
The table columns are the same name as a variable. That’s a good pattern, and I’d think SQL Prompt could handle that.
It doesn’t.
The column picker doesn’t work with Updates (logged w/ product team), and I can’t duplicate selected text over (also logged for discussion). However, I do have a workaround.
As I thought about it, I realized there are some features of Prompt that help here, and some of SSMS that will work.
I made a quick video of the process, but I’ll describe it below:
The Process
The first thing is to get a column list. ssf<tab> does for me. I’ll get the select statement for a table and then expand the list of columns with a tab when on the *.
Now, I’ll copy the columns. I tend to copy all since it’s usually easier to remove than pick and choose specific ones. I’ll wrap these in an update, which could be a snippet. If it’s not, that’s fine.
From here, I use the power of Shift+ALT. If you’ve never done this, it’s amazing. I use this to select the columns and copy them. Then I’ll CTRL+ALT to add the = and paste in the columns. I can then use CTRL+ALT once again to remove the alias and replace with a @.
And, of course, I can reformat to make it look nice with SQL Prompt. Give SQL Prompt a try today and see how it can improve coding and feel free to share your tips here.