如何为简单的ETL过程正确地使用事务范围？

Question

这与Server 2008有关。

我正在编写一个过程，为BI目的将数据从生产数据库移动到周期性缓存数据库。

这两个数据库都位于同一个SQL实例上，因此我计划在此阶段避免使用SSIS。

我需要：

1. 从缓存中删除所有现有数据。
2. 选择一个表中的所有业务分支。
3. 选择一个表中的所有销售部门
4. 选择并转换实际数据。
5. 如果2-4中的任何一个失败，那么我需要回滚并返回文本错误。这样，缓存中的数据将保持在调用过程之前的状态。

我看过了：Stored Procedure Transaction

我想尝试，抓住可能是过分的。

这方面的最佳做法是什么？它是否像使用

BEGIN TRANSACTION

-- Do stuff

IF @@ERROR <> 0
    ROLLBACK TRANSACTION

COMMIT TRANSACTION

编辑-这篇文章也非常有用：How does SQL Server treat statements inside stored procedures with respect to transactions?

Answer 1

我将使用以下方法：

首先，我会在存储过程中使用TRY CATCH语法。

我不会删除所有现有数据，而是重命名表(缓存、业务分支和销售部门)，并创建具有相同名称和格式的新表。我不知道第四步有多复杂。但是，如果它是直接的，我会将它放在TRY CATCH块中的一个单独的事务中，以允许回滚。

如果任何代码块失败，它将跳转到CATCH部分。在该部分中，我将回滚步骤4(如果需要)，删除新创建的表并重命名旧表。

编辑:如果没有任何错误，删除重命名的表。

Answer 2

至于事务性部分，我会选择一个try catch解决方案来处理事务作用域

http://msdn.microsoft.com/en-us/library/ms175976.aspx

BEGIN TRANSACTION;

BEGIN TRY
    -- Generate a constraint violation error.
    DELETE FROM Production.Product
    WHERE ProductID = 980;
END TRY
BEGIN CATCH
    SELECT 
        ERROR_NUMBER() AS ErrorNumber
        ,ERROR_SEVERITY() AS ErrorSeverity
        ,ERROR_STATE() AS ErrorState
        ,ERROR_PROCEDURE() AS ErrorProcedure
        ,ERROR_LINE() AS ErrorLine
        ,ERROR_MESSAGE() AS ErrorMessage;

    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;
END CATCH;

IF @@TRANCOUNT > 0
    COMMIT TRANSACTION;
GO