I want to edit some row values once I get a query output in the result grid. Its true that we can right click the table and say open table to get an editable table output, but what I want is editable query output, only certain rows matching for my criteria, and edit them in the result grid.
Can this possible inside Microsoft SQL server Management Studio Express?
This question is related to
ssms
The answer is
Yes, This is possible. Right click on the table and Click on Edit Top 200 Rows as show in image below
Then click anywhere inside the result grid, to enable SQL Icon "Show Sql Pane". This will open sql editor for the table you opted to edit, here you can write your own sql query and then you can directly edit the result set of the query.
If you need to frequently perform in-cell edits on SQL databases, HeidiSQL works a treat, couldn't be simpler to use, and is free / open source (donations accepted).
Originally written for MySQL, it can now handle SQL Server, and has experimental (as of Aug 2014) PostgreSQL support as well.
You can do something similar to what you want. Right click on a table and select "edit top 200 rows" (if you are on SQL Server 2008) or "open table" in SQL Server 2005. Once you get there, there is a button on the top that says "SQL"; when you click on it, it lets you write an SQL statement and you can edit the results of it if you click a cell you want to change.
To be clear: The option "Value for Edit Top Rows command" has nothing to do with the fact if a result set is editable or not. It is just a way to limit the result set.
Editing the result set of a query based on one and only one table is obviously always possible.
The result set of a query based on more than one table is under following condition possible: You can edit the fields in the result set at once if they belong to one and only one based table in the query! If the fields are Primary Key, then you have to fulfill refresh/"Execute SQL" (Ctrl+R) after each row update, in order to be able to edit a row next time. If the fields are not Primary Key, then you do not need to fulfill refresh/"Execute SQL" (Ctrl+R).
I have tested it on SQL Server 2008 - 2016!
Just choose "Edit Top 200 rows", press Ctrl + 3 in the edit grid region (or click "Show SQL Pane") and edit the query...
But please note that this will work only for the query that doesn't contain "join"
If the query is written as a view, you can edit the view and update values. Updating values is not possible for all views. It is possible only for specific views. See Modifying Data Through View MSDN Link for more information. You can create view for the query and edit the 200 rows as given below:
SSMS - Right Click Results of Edit 200 | Option | Pane | SQL - edit the statement.
First of all right click the tale select 'Edit All Rows', select 'Query Designer -> Pane -> SQL ', after that you can edit the query output in the grid.
The way you can do this is by:
- turning your select query into a view
- right click on the view and choose
Edit All Rows(you will get a grid of values you can edit - even if the values are from different tables).
You can also add Insert/Update triggers to your view that will allow you to grab the values from your view fields and then use T-SQL to manage updates to multiple tables.
No. There is no way you can edit the result grid. The result grid is mainly for displaying purposes of the query you executed.
This for the reason that anybody can execute complex queries. Hopefully for the next release they will include this kind of functionality.
I Hope that answer your question.
UPDATE
as you can see correct solution in Learning answer,
In SQL server management 2014 you can
1.click on "Edit Top 200 Rows"
and then
2.clicking on "Show SQL Pane (ctrl+3)"
and
3.removing TOP (200) from select query
Refer to Shen Lance answer there is not a way to edit Result of select query. and the other answers is only for normal select and only for 200 records.
The given answers are still valid. No change in SSMS (SQL Server 2016) has been made on that regard.
You can also use the criteria pane, after doing the "Edit Top 200 Rows".
- Show criteria pane
- Enter some criterion
- Edit data directly in the results grid
Additionally, the number of rows for those commands can be customized in your SSMS options.
Yes you can edit joined results. (at least in SSMS 2008 R2) After you edit any of the result values in the View that uses joins, you'll need to execute the query again to refresh the results.
You also need to make sure SSMS is configured to allow "Edit All Rows" ... to do this in SSMS - Tools | Options | SQL Server Object Explorer | Commands ... expand the Table and View Options ... put a value of 0 in "Value for Edit Top n Rows command" ... can do this for the select also.
Yves A Martin's response is 100% correct!
Rob
Right click on any table in your dB of interest or any database in the server using master if there are joins or using multiple dBs. Select "edit top 200 rows". Select the "SQL" button in the task bar. Copy and paste your code over the existing code and run again. Now you can edit your query's result set. Sherry ;-)
Source: Stackoverflow.com