SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Can you preview a transaction before committing?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Goalie35
Yak Posting Veteran

78 Posts

Posted - 08/15/2013 :  12:01:49  Show Profile  Reply with Quote
Yesterday, my co-worker was working on running some update queries to some of our tables. On one update however, he accidentally updated the wrong field to a few hundred thousand records, so we had to go in & restore our backup to fix the problem. Needless to say, he landed in a lot of hot water. He was just running basic queries in Mgmt Studio. Ran his "begin transaction", saw he affected the correct number of records, then committed the transaction, not realizing he updated the wrong field.

So I was curious, does Management Studio have a tool or something where I can preview the results of a transaction before committing? So, if I were to run an update for example, I would do a "begin transaction", click something to preview the results & make sure it looks ok, then I could commit the transaction?

Thanks

James K
Flowing Fount of Yak Knowledge

3549 Posts

Posted - 08/15/2013 :  12:05:37  Show Profile  Reply with Quote
Do it like this first:
BEGIN TRAN
	UPDATE YourTable SET XYZ = 1.0;
	SELECT * FROM YourTable;
ROLLBACK;
That will show you the modified rows, inspect them, and if you are satisfied, then
BEGIN TRAN
	UPDATE YourTable SET XYZ = 1.0;
	--SELECT * FROM YourTable;
--ROLLBACK;
COMMIT
The only thing is that, if the updates affect a lot of rows, the updates will take time, and the rollback will take time.
Go to Top of Page

Goalie35
Yak Posting Veteran

78 Posts

Posted - 08/15/2013 :  12:29:22  Show Profile  Reply with Quote
Perfect. Thanks James!
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000