How To Edit Table Data In Sql Server Management Studio

– How to edit MS SQL records with filtering on SQL Management Studio

Editing SQL records from the back cease is not recommended and should only be done with extreme caution. It is not for the faint of centre and exclusively the task of the database administrators. Changes to the information should just be washed with the provided front stop awarding so that any lawmaking behind can run to cleanse the data entry prior to saving. However, as developers and testers, we notice ourselves with the need to go down and dirty with data editing directly on the records. We can practice this with the SQL UPDATE command directly on the Query window. Powerful as it is, this may become tiresome at times to perform. If nosotros tin can edit the records visually with minimal coding, so that might be a ameliorate alternative.

Here are ways that you can edit the data "visually". Once again, this is only for development purposes and granted that y'all know what you lot are doing. J

1. You can always edit the table by correct clicking on the table and selecting Edit top <number> Rows. See the sample below.

Edit Context Menu

2. On the sample above, if y'all need to see and edit the peak 100 all the time, then yous could specify this on the Tools -> Options -> SQL Server Object Explorer -> Commands. See the sample below:

SQL Server Options

three. Now, what if the table contains thousands of records and you lot simply want to edit a subset. For example: Person.Address table on AdventureWorks2008R2 database contains 19,614 records. Nevertheless, you only wanted to edit records whose Urban center is "Bothell". Doing steps 1 and ii are not sufficient as there is no filtering involved. You demand to have a third pace to enable filtering.

4. Right clicking on the selected table and select Edit tiptop <number> Rows on the context carte du jour. See the sample below.

Edit Context Menu

5. You should have the brandish similar to the 1 below:

Editable Rows

6. On the carte du jour, keep to Query Designer -> Pane -> SQL. The window shown below should be displayed:

Editable Rows With Select

vii. As you tin see, a pane is added on elevation and SQL provided y'all with the SQL query that resulted in the peak 200. At present, all you take to practise is add together your WHERE filter to the SELECT command. See sample below:

Editable Rows With Select Filter

The query returns 26 records just and filtered to evidence all records having "Bothell" equally the urban center. Now you can showtime editing the displayed records by clicking on the cell y'all desire to edit (except for the Primary Key).

SELECT, SQL Server Management, SQL Server Management Studio, UPDATE, Update Database

This entry was posted on September 25, 2011, ix:19 pm and is filed nether Microsoft SQL Server - Administration, Microsoft SQL Server - Programming. You can follow any responses to this entry through RSS two.0. You can leave a response, or trackback from your own site.