如何预览破坏性 SQL 查询? [英] How do I preview a destructive SQL query?
问题描述
在 SQL Server Management Studio 中编写破坏性查询(例如，DELETE 或 UPDATE)时，我总是希望能在不实际运行查询的情况下预览查询结果.Access 非常方便地允许您执行此操作，但我更喜欢手动编写 SQL 代码，遗憾的是，Access 在这方面非常差.
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:
是否有 SSMS 的附加组件，或者一个单独的工具，它配备了良好的 SQL 手工编码工具，还可以预览破坏性查询的结果，类似于 Access?
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?
是否有手动"进行预览的任何技术或最佳实践；例如，以某种方式使用事务?
Are there any techniques or best practices for doing previews "by hand"; e.g., somehow using transactions?
在我看来，做这类事情从根本上来说很重要，但我似乎无法通过 Google 找到任何东西(我可能只是在寻找错误的东西 - 我对这件事非常无知).目前，我正在采取一种相当多毛的腰带和大括号方法来评论选择/删除/更新行并确保我进行备份.肯定有更好的方法吗?
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?
有人可以帮忙吗?
推荐答案
我会使用 SQL SERVER 2008 以后的 OUTPUT 子句...
I would use the OUTPUT clause present in SQL SERVER 2008 onwards...
有点像……
BEGIN TRANSACTION
DELETE [table] OUTPUT deleted.* WHERE [woof]
ROLLBACK TRANSACTION
INSERT 和 UPDATE 也可以使用插入"表.MSDN 文章涵盖了所有内容.
INSERTs and UPDATEs can use the 'inserted' table too. The MSDN article covers it all.
这就像在事务中 SELECT 然后 DELETE 的其他建议一样，除了它实际上是同时进行的.因此，您打开一个事务，使用 OUTPUT 子句删除/插入/更新，并在输出所做的更改的同时进行更改.然后你可以选择回滚或提交.
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.
这篇关于如何预览破坏性 SQL 查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
