Rolling back truncate table

I have a stored procedure that does the following:

BEGIN TRANSACTION

    -- Code to delete updated records from production (dbo) table
    DELETE FROM [dbo].[factMyTable]
    WHERE exists (SELECT *
        FROM [RAW].[MyTable]
        WHERE [RAW].[MyTable].[refno] = [dbo].[factMyTable].[refno]
        AND [RAW].[MyTable].[modification_dttm] >= [dbo].[factMyTable].[modification_dttm]
        )

    -- Code to perform the append of incremental records 
    INSERT INTO [dbo].[factMyTable]
    SELECT
         [refno]
        ,[field1]
        ,[field2]
        ,[field3]
        ,[FieldN]
        ,[modification_dttm]
    FROM [RAW].[MyTable]

-- Truncate stage table and get ready for next load
TRUNCATE TABLE [RAW].[MyTable]

COMMIT TRANSACTION

As you can see above, I have a truncate command that is contained within a BEGIN/COMMIT transaction block. However I got an error when executing this stored procedure at the insert command, where a field set as NOT NULL was receiving a NULL value. As a result:

• The insertion of records from the RAW table into the dbo table got rolled back; BUT

• The truncation of the RAW table did not roll back.

The idea is that if there is an error with the insertion of data, the truncation shouldn't happen.

According to this article, we can roll back the truncate command, but perhaps my stored procedure is not scripted correctly. Perhaps there is a more direct way of ensuring that the truncation only happens if the insertion returns no errors? How would I go about it?

• The insertion of records from the RAW table into the dbo table got

rolled back; BUT

• The truncation of the RAW table did not roll back.

No, there was no rollback at all, and here is the repro.

With xact_abort off that is your default set option I created 2 tables, I then open transaction end do 2 inserts one of which(the second one) fails, I added select @@trancount and select from both tables so you can better see what happens:

--set xact_abort on

if object_id('dbo.t1') is not null drop table dbo.t1;
if object_id('dbo.t2') is not null drop table dbo.t2;
go

create table dbo.t1 (col1 int);
insert into dbo.t1 values(1), (null);

create table dbo.t2 (col1 int not null);
go

begin transaction

    insert into dbo.t2
    values(-1);

    insert into dbo.t2
    select col1
    from dbo.t1;

    select @@trancount as [@@trancount before truncate];

    truncate table dbo.t1;

commit transaction;

select @@trancount as [@@trancount after commit];

select *
from dbo.t1;

select *
from dbo.t2;

As you see, no rollback was made, only your commit. You insert (-1) into dbo.t2 and this row is permanently there. This is because the error


Msg 515, Level 16, State 2, Line 18 Cannot insert the value NULL into
column 'col1', table 'dbo.t2'; column does not allow nulls.

is statement terminating only. The second statement fails so no row were inserted, but insert of (-1) was not rollbacked, and as you see after the error your transaction is still open. It's your commit that commits insert of -1 and table truncation.

Now the second test: uncomment set xact_abort on, this will make statment terminating only error be batch aborting, all the statements within transaction will be rolled back and execution will be interrupted as soon as the error occurs.

So t1 table will never be truncated and the insert of (-1) in t2 will be rolled back.

And now how your code should be written:

set xact_abort on;

if object_id('dbo.t1') is not null drop table dbo.t1;
if object_id('dbo.t2') is not null drop table dbo.t2;
go

create table dbo.t1 (col1 int);
insert into dbo.t1 values(1), (null);

create table dbo.t2 (col1 int not null);
go

begin try
begin transaction

    insert into dbo.t2
    values(-1);

    insert into dbo.t2
    select col1
    from dbo.t1;

    select @@trancount as [@@trancount before truncate];

    truncate table dbo.t1;

commit transaction;
end try

begin catch
    select @@trancount as [@@trancount in catch before rollback];
    if @@rowcount > 0 rollback;
    throw;
end catch;

Your code should always set xact_abort on and it should have try..catch block.

You should do rollback from catch ad throw the error

StackExchange Database Administrators Q#254152, answer score: 4