Number of Records affected

Question

Number of Records affected

jayoub

SSCertifiable

Points: 6991
More actions
December 8, 2010 at 1:05 pm

#388399

Is there a way that in SSMS query window I can determine how many records are effected by an update or insert query before running it.
I support a software that uses SQL for the database and the software has its own query window. The great think about this query window is that when i run any type of Update or Insert query it will let me know how many records are affected and give me a chance to "commint changes" (YES, NO). Clicking YES will run the query clicking NO will Cancel.
Can I have this in SSMS query window and how.
Please let me know
Thanks
Jeff
Jeff

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

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

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 1

jayoub1 (12/8/2010)
Is there a way that in SSMS query window I can determine how many records are effected by an update or insert query before running it.
I support a software that uses SQL for the database and the software has its own query window. The great think about this query window is that when i run any type of Update or Insert query it will let me know how many records are affected and give me a chance to "commint changes" (YES, NO). Clicking YES will run the query clicking NO will Cancel.
Can I have this in SSMS query window and how.
Please let me know
Thanks
Jeff

That Yes/No window is actually a chance to COMMIT/ROLLBACK TRANSACTION. I don't believe it runs the query after you click 'yes'.

The way to mimic that in SSMS is to put a BEGIN TRAN at the beginning of your script, and don't include a rollback or commit at the end until you've checked the values and actually type it in and run that separately in the same window.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

jayoub SSCertifiable Points: 6991 More actions · Answer 2

Yes, you are correct about the rollback because if i click no the window says Rolled back transactions.

I dont understand how you said to do it in SSMS lets say for example i am typeing the following query

UPDATE CMLEDG SET TRANDATE = '12/01/2010' WHERE BLDGID = '600' AND PERIOD = '201012'

How would i write this in the SSMS window so it would tell me how many records in the table will be affected before committing the update.

Thanks for you help

Jeff

GSquared SSC Guru Points: 260824 More actions · Answer 3

begin transaction;

UPDATE CMLEDG SET TRANDATE = '12/01/2010' WHERE BLDGID = '600' AND PERIOD = '201012';

Run that. Check the numbers. Then add Commit if you want to keep it and run the commit on its own (select and execute will do that).

My usual practice is:

begin transaction;

UPDATE CMLEDG SET TRANDATE = '12/01/2010' WHERE BLDGID = '600' AND PERIOD = '201012';

rollback;

Then check the numbers, and if they are correct, change to commit and re-run. It means I don't accidentally leave a transaction open if I get pulled away from my desk or anything like that.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

jayoub SSCertifiable Points: 6991 More actions · Answer 4

jayoub

SSCertifiable

Points: 6991

December 8, 2010 at 1:42 pm

#1260308

I understand. Thanks for the help

Jeff

Lamprey13 SSCertifiable Points: 6210 More actions · Answer 5

you can just change the query to a select and check the count:SELECT COUNT(*) FROM CMLEDG WHERE BLDGID = '600' AND PERIOD = '201012';