TSQL合并错误处理 [英] TSQL Merge error handling

问题描述

在运行merge语句时需要帮助处理错误。
如何使合并的行为就像在每个处理过的行周围都有一个try-catch块一样
（如果遇到错误，请捕获该错误并将其记录（或任何其他处理），然后继续处理下一行）？
Merge语句的默认行为是什么，当发生错误时它会回滚整个事务还是继续处理记录？
可以使用输出子句检查错误吗？

Need help handling errors while running a merge statement. How do I make the merge behave as if it has a try-catch block surrounding each row processed ( If an error is encountered, catch that error and log it (or any other processing) then continue processing the next rows)? What is the default behavior of the Merge statement, does it rollback the whole transaction when an error occurs or it continues processing the records? Can the output clause be used to check for errors?

推荐答案

MERGE，与所有其他DML语句一样（插入，UPDATE，DELETE）是一个全有或全无的命题-它要么整体成功，要么失败（并且运行于其中的隐式事务（如果已打开的话）将被回滚）。

MERGE, as with all other DML statements (INSERT, UPDATE, DELETE) is an all or nothing proposition - either it succeeds in it's entirety, or it fails (and the implicit transaction it's running within, if such a one has been opened, is rolled back).

如果将出现实际错误（例如，外键约束破损，检查约束，截断），则需要事先进行处理，或者更改MERGE，以免发生错误（例如，明确截断超长数据）。如果MERGE语句的源是一个复杂的查询，并且数据清理可能很复杂，则您可能希望将结果暂存到一个临时表中以执行这种清理。

If there are going to be actual errors (e.g. broken foreign key constraints, check constraints, truncation), you need to deal with these beforehand, or alter your MERGE so that the error will not occur (e.g. explicitly truncate overlong data). If the source of your MERGE statement is a complex query, and the data cleanup is likely to be complex, you might want to stage the results into a temporary table to perform such cleanup.

这篇关于TSQL合并错误处理的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！