October 02, 2009

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

image

I get the following results:

image

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:

image

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

image

And here’s what you get

image


Did you like this post?
kick it on DotNetKicks.com
subscribe via rss subscribe via e-mail
print this post follow me on twitter


About The Author

Suprotim Agarwal, ASP.NET Architecture MVP works as an Architect Consultant and provides consultancy on how to design and develop Web applications.

Suprotim is also the founder and primary contributor to DevCurry, DotNetCurry and SQLServerCurry. He has also written an EBook 51 Recipes using jQuery with ASP.NET Controls.

Follow him on twitter @suprotimagarwal

 
  Feedback:

comments

2 Responses to "Specify a Condition while Editing TOP Rows using SQL Server 2008 Management Studio (SSMS)"
  1. psy said...
    October 4, 2009 at 6:20 AM

    You do this the same way you have done in previous sql management studio versions.

    Creating a view is a very bad solution as it alters the database to solve an editor issue.

    Here is the correct solution:
    * 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.

  2. Suprotim Agarwal said...
    October 4, 2009 at 11:23 AM

    psy -- I completely agree that view is not a very good solution.

    Thanks for sharing the right solution with the users. I have updated the post to reflect it.

 

Copyright © 2009-2014 All Rights Reserved for SQLServerCurry.com by Suprotim Agarwal | Terms and Conditions