User Tools

Site Tools


notes:sql:savepoint

Transaction Savepoint in SQL Server

This is an example of using a transaction savepoint:

DROP TABLE Test1
GO
 
CREATE TABLE Test1
(
  Code CHAR(3)
)
 
SET NOCOUNT ON
 
BEGIN TRAN
 
INSERT INTO Test1 VALUES('aaa')
SELECT * FROM Test1 -- aaa
 
SAVE TRAN P1
INSERT INTO Test1 VALUES('bbb')
SELECT * FROM Test1 -- aaa,bbb
 
SAVE TRAN P2
INSERT INTO Test1 VALUES('ccc')
SELECT * FROM Test1 -- aaa,bbb,ccc
 
SAVE TRAN P3
INSERT INTO Test1 VALUES('ddd')
SELECT * FROM Test1 -- aaa,bbb,ccc,ddd
 
ROLLBACK TRAN P2
SELECT * FROM Test1 -- aaa,bbb
 
ROLLBACK TRAN P1
SELECT * FROM Test1 -- aaa
 
INSERT INTO Test1 VALUES('xxx')
SELECT * FROM Test1 -- aaa,xxx
 
COMMIT TRAN
 
SELECT * FROM Test1 -- aaa,xxx
 
SET NOCOUNT OFF
Code
----
aaa
 
Code
----
aaa
bbb
 
Code
----
aaa
bbb
ccc
 
Code
----
aaa
bbb
ccc
ddd
 
Code
----
aaa
bbb
 
Code
----
aaa
 
Code
----
aaa
xxx
 
Code
----
aaa
xxx
notes/sql/savepoint.txt · Last modified: 2020/08/26 (external edit)