Can I open a Table and Edit Data like I did in Access?

Question

Post reply

Can I open a Table and Edit Data like I did in Access?

g_dion

SSC Journeyman

Points: 94
More actions
February 4, 2010 at 1:55 pm

#386245

I am new to SQL, but have some experience with Access. In Access, I could open up a Table and edit data LIVE, as if it was a spreadsheet. Can I do this is SQL 2005? I have this bad feeling that I need to create some type of update/Where statements, but wanted to get clarification.
I am learning how to write update queries, but am nowhere near proficient enough to manipulate data
Thanks in Advance
Grant

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Ken Jr. SSCommitted Points: 1842 More actions · Answer 1

Yes you can. Just right click on the table you want to update in management studio and choose 'open'.

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 2

In SQL 2000 that would probably be right.

In SQL 2005 you right click and choose "Edit Top 200 Rows". This is the limitation.

However, I would say you should only use this for discrete quick changes and get in the habit of editing data with update statements.

CEWII

Jeffrey Williams SSC Guru Points: 90304 More actions · Answer 3

Unfortunately, that functionality still exists in SSMS but I caution against using it at all. It is rather buggy and can actually update data incorrectly.

Work on understanding insert, update and delete statements. They will be much more reliable in the long run.

One of the things you should get in the habit of is structuring your modifications as:

USE {yourdatabasehere}; -- make sure you are in the right database

GO

BEGIN TRANSACTION; -- start a transaction

-- show what we are going to update/delete/insert before changes

SELECT {columns}

FROM {yourtable}

WHERE othercolumn = 'othervalue'; -- this should match update/insert/delete statement

-- perform the update

UPDATE {yourtable}

SET column1 = 'somevalue'

WHERE othercolumn = 'othervalue';

-- show the changes after the update/delete/insert

SELECT {columns}

FROM {yourtable}

WHERE othercolumn = 'othervalue';

/*

ROLLBACK TRANSACTION; -- roll back changes as we test

COMMIT TRANSACTION; -- uncomment this line and comment out the rollback when results

are correct and as expected

*/

GO

The idea in the above example is to explicitly start a transaction that has to explicitly be committed for the changes to take place. This way, when you first run your update and it changes 400,000 rows instead of the 4 rows you wanted - the changes are rolled back and you have a chance to correct the script.

As you'll notice, the rollback/commit is commented out. After reviewing the changes I'll manually execute the rollback or the commit depending upon whether or not the update worked as I expected. When testing - I'll un-comment the rollback so it is done right away and I can review the before and after results.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs