Specify a Condition while Editing TOP Rows using SQL Server 2008 Management Studio (SSMS)

I had recently posted about Using SSMS to change the Edit TOP Rows option . A user Greg commented asking me if it was possible to specify a condition while Editing the TOP Rows.

Here’s my observations. I am using the Purchasing.VendorContact table from the AdventureWorks database as a sample. Right click on the VendorContact table > Edit Top 5 rows

I get the following results:

Now let us say that using SSMS, you want to Edit only those rows having ContactTypeID=2. SQL Server 2008 gives you no option out of the box to do so – at least I couldn’t find one.

Here’s how I solved Greg’s requirement.

Update: Psy has shared the right way to do this. Follow these steps:

* Click edit top x rows. Click the "view sql pane" button (the letters SQL in a white box) from the query designer toolbar that appears in edit mode.

* Change the sql as needed.

* Click the red exclaimation mark to re-query the dataset.

* Edit as needed.

* You can also click the 'view sql pane' a 2nd time to hide it and still keep the changed query. You can also hit the execute icon as many times as needed to refresh the data.

The other way to handle this requirement is to Create a View! However this solution should be avoided:

Right click Views > New View. Type the following query:

Save this view as ‘EditVendorContact’. Now Right Click this newly created view > Edit Top 5 Rows

And here’s what you get