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.