sql server - How do I preview a destructive SQL query?

Question

When writing destructive queries (e.g., DELETE or UPDATE) in SQL Server Management Studio I always find myself wishing I could preview the results of the query without actually running it. Access very conveniently allows you to do this, but I prefer to code my SQL by hand which, sadly, Access is very poor at.

So my question is twofold:

1. Is there an add-on for SSMS, or a separate tool that is furnished with good SQL hand-coding facilities that can also preview the result of a destructive query, similar to Access?

2. Are there any techniques or best practices for doing previews "by hand"; e.g., somehow using transactions?

It seems to me that doing this sort of thing is fundamentally important but yet I can't seem to find anything via Google (I'm probably just looking for the wrong thing - I am terribly ignorant on this matter). At present I'm taking a rather hairy belt and braces approach of commenting in and out select/delete/update lines and making sure I do backups. There's got to be a better way, surely?

Can anyone help?

question from:https://stackoverflow.com/questions/465083/how-do-i-preview-a-destructive-sql-query

Answer 1

I would use the OUTPUT clause present in SQL SERVER 2008 onwards...

OUTPUT Clause (Transact-SQL)

Something like...

BEGIN TRANSACTION

DELETE [table] OUTPUT deleted.* WHERE [woof] 

ROLLBACK TRANSACTION

INSERTs and UPDATEs can use the 'inserted' table too. The MSDN article covers it all.

EDIT:

This is just like other suggestions of SELECT then DELETE inside a transaction, except that it actually does both together. So you open a transaction, delete/insert/update with an OUTPUT clause, and the changes are made while ALSO outputting what was done. Then you can choose to rollback or commit.