September 19, 2012 at 11:05 am
Can someone demonstrate for me how to rollback a TRUNCATE command when a subsequent INSERT statement fails.
CREATE TABLE Test (Test char(1))
INSERT INTO Test SELECT 'A' UNION SELECT 'B'
TRUNCATE TABLE Test
--If this insert fails I want to rollback the truncate
INSERT INTO Test SELECT 'CC'
Do I need to test for errors or perform a record count on the table to and then implement the rollback? Or can SQL Server detect an error and rollback on its own.
Thanks
September 19, 2012 at 11:13 am
This?
CREATE TABLE Test (Test char(1));
go
INSERT INTO Test SELECT 'A' UNION SELECT 'B';
go
select * from Test;
go
begin try
begin transaction
TRUNCATE TABLE Test;
--If this insert fails I want to rollback the truncate
INSERT INTO Test SELECT 'CC';
commit
end try
begin catch
rollback transaction
end catch
select * from Test;
go
drop table test;
go
September 19, 2012 at 11:18 am
Yes, exactly, thank you once again.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply