Open Table with Query to modify data

In Enterprise Manager, I would right click on the table, choose Open Table and Query where I could select specific records and (most importantly) could alter data in a record by deleting the text, adding or over-typing.

In 2005 Server Management Studio I just cannot figure how to do this. I'm guessing that I need the 'Script Table as' option but then what?

I have managed to open selected data using the New Query and then Design Query in Editor, but the results only appear in a kind of view form and I cannot seem to alter any of the data entries, I get dotted lines around the selected field.

Please help, it seemed so much easier in 2000!

(1) Open the database folder for the database that contains the target table; (2) locate the target table within the database; (3) right click on the target table; (4) select the "Open Table" option; (5) the table is opened and can now be edited.

See if this also works for you.

|||

Hi

Thanks for your reply, my problem is that the table we open most often in order to edit/change fields, has over 1.5 million record rows! So we're not keen on opening the whole table. The problem with using a query is that the results can't be edited.

|||YOu can, but it is sort of hidden in the toolbox pane. Use Open table > Stop the processing of the data rows (as you do not want to wait for 1,5M rows) using the red square at the bottom of the page. Switch on the SQL Pane using the little tool in the left corner (Show SQL Pane) > Modify your query to limit the results > Execute again > You should now be able to edit the data in the results pane.

Jens K. Suessmeyer

http://www.sqlserver2005.de
|||

Hi

Have done that and it works however, with more complex queries we prefer to use the Query Editor, selecting the field names and criteria in the grid and allowing the system to write the sql. Even with seemingly simple things like specifying a date time we are having to write the Convert DateTime instruction which is time consuming and somewhat frustrating.

I have worked out a stop gap solution in that I

1. open a New Query and then click the icon for Design Query in Editor

2. use the editor to write the query, copy the generated sql

3. open the table, stop the run of all records

4. Show the SQL pane and paste the query sql into it and run in order to get results that I can edit

It's not perfect and I have the problem that if I want to go back and adjust the query, I cannot open the original selection in Query Editor as it closes down after you have used it. I have to open up another new one and start again!

You could do all this in one window in Enterprise Manager, run a query, get 'editable' results and go back to the query to adjust it if need be, again getting 'editable' results.

Why have they removed this in 2005 and is it going to be resolved because I've now found other threads from users with the same problem.

Thanks

Annie

|||Well post it as a connect suggestion on http://connect.microsoft.com/sqlserver/Feedback :-)

Jens K. Suessmeyer

http://www.sqlserver2005.de
|||

Thanks Jens

Sorry we have only just set up the 2005 server and I am new to the forum so wasn't aware of the Connect site, but I will do that now.

Thanks again for the help