locked
Rollback and Commit Transaction RRS feed

  • Question

  • User601449031 posted

    I have a scenario like this in SQL Server.

    1.Have four tables T1,T2,T3,T4

    2.Should delete all data from T1,T2

    3.Copy Data from T3,T4 into T1 and T2

    4.If any error occurs while copying data in step 3, i should rollback the transaction .

    5.If the Data get copied successfully, should commit the transaction

    I have written the code here. Is it correct? Pls check.

    SET NOCOUNT ON

    DECLARE @TransactionName varchar(20) = 'Transaction1';
    DECLARE @ROWCOUNTUK INT
    DECLARE @ROWCOUNTROI INT

    BEGIN  TRANSACTION @TransactionName

        TRUNCATE TABLE dbo.ref_TPNB_UK
        TRUNCATE TABLE dbo.ref_TPNB_ROI
        
        INSERT INTO ref_TPNB_UK SELECT * FROM dbo.ref_TPNB_UK_Temp
        SET @ROWCOUNTUK=@@ROWCOUNT
        INSERT INTO ref_TPNB_ROI SELECT * FROM dbo.ref_TPNB_ROI_Temp
        SET @ROWCOUNTROI=@@ROWCOUNT
        
        IF((@ROWCOUNTUK > 0) AND (@ROWCOUNTROI > 0))
            BEGIN
                TRUNCATE TABLE dbo.ref_TPNB_UK_Temp
                TRUNCATE TABLE dbo.ref_TPNB_ROI_Temp
                COMMIT TRANSACTION @TransactionName
            END
        ELSE
            BEGIN
                ROLLBACK TRANSACTION @TransactionName
            END    
    END    

    Thursday, August 23, 2012 4:23 AM

Answers

  • User1428339021 posted

    you have truncate statement to delete the data.

    Truncate statement cannot be rolled back. Use Delete instead.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, August 23, 2012 4:50 AM