Savepoint command in SQL Server 2008

In this article I am going to explain how to use Savepoint command in SQL Server 2008.
  • 12196

Introduction

Savepoint command in SQL Server is used  to rollback the transaction into a certain point. Sometimes a situation arises In which a user want to modify a table into a database. A user wants to delete some records into the database. After deleting a record into a database the user want to reached the old point in the transaction we can used the save point.

The syntax for SAVEPOINT command is as follows

SAVEP Transaction  SAVEPOINT NAME;

The syntax for rolling back to a SAVEPOINT is as follows

ROLLBACK Transaction SAVEPOINT_NAME;
 

Consider table(mcnnew ) is having following records:

create table mcnnew
(
id int not null,
Name varchar(15),
Age int,
salary varchar(25),
)

The records showing in following figure

 Clipboard02.jpg

Delete the record from the mcnnew

Delete the record from mcnnew where ID = 3

and ID = 4;

Save the Points

begin tran del
save tran p1
delete from mcnnew where id =3

 

Show the result

Clipboard03.jpg

 
begin tran de
save tran p2
delete from mcnrep where id = 4
 

Show the result

Clipboard04.jpg

Roll Back to the certain point

Rollback transaction to the P2 

rollback tran p2

Show the result

 Clipboard05.jpg

Rollback transaction to the P1

rollback tran p1

Show the result

Clipboard02.jpg

Categories

More Articles

© 2020 DotNetHeaven. All rights reserved.