Stored Procedure - Doing Insert and delete as single workable unit
-
Hi,
i've these following table,
Before transaction
REPLAY_LOG
ACTIVITYID | Student
-------------------
2 | James
2 | William
3 | Marsden
3 | Rooney
After transaction
REPLAY_LOG_NEW
ACTIVITYID | Student
-------------------
2 | James
2 | William
REPLAY_LOG_ARCHIEVE
ACTIVITYID | Student
----------------------------------
3 | Marsden
3 | Rooney
Flow - all rows in REPLAY_LOG insert into REPLAY_LOG_NEW. then, move 3|Marsden and 3|Rooney to REPLAY_LOG_ARCHIEVE.
How to do the best way for T-SQL (INSERT and Delete) in Stored Procedure apply rollback (if any transaction fail, no table will change) and commit (need both table success do the transaction)
-
Here you go.
Declare @i_Error int
set nocount on
set @i_Error = 0
Begin Tran TR1
Insert into REPLAY_LOG_NEW(ACTIVITYID , Student)
select ACTIVITYID , Student from REPLAY_LOG
Select @i_Error = @@ERROR
If @i_Error = 0
Begin
Insert into REPLAY_LOG_ARCHIEVE(ACTIVITYID , Student)
select ACTIVITYID , Student from REPLAY_LOG_NEW where ACTIVITYID =3
Select @i_Error = @@ERROR
If @i_Error = 0
Begin
Commit Tran TR1
End
Else
Begin
Rollback Tran TR1
End
End
Else
Begin
Rollback Tran TR1
End
set nocount off
This should work for you.
Thanks
Sreejith
-
I did not see the Delete statement to clear REPLAY_LOG_NEW become
REPLAY_LOG_NEW
ACTIVITYID | Student
-------------------
2 | James
2 | William
According to your SQL Scripts, REPLAY_LOG_NEW have a same data with REPLAY_LOG.
where i need to put delete statement?
-
Declare @i_Error int
set nocount on
set @i_Error = 0
Begin Tran TR1
Insert into REPLAY_LOG_NEW(ACTIVITYID , Student)
select ACTIVITYID , Student from REPLAY_LOG
Select @i_Error = @@ERROR
If @i_Error = 0
Begin
Insert into REPLAY_LOG_ARCHIEVE(ACTIVITYID , Student)
select ACTIVITYID , Student from REPLAY_LOG_NEW where ACTIVITYID =3
Select @i_Error = @@ERROR
If @i_Error = 0
Begin
Delete REPLAY_LOG_NEW where ACTIVITYID =3
Select @i_Error = @@ERROR
If @i_Error = 0
Begin
Commit Tran TR1
End
Else
Begin
Rollback Tran TR1
End
End
Else
Begin
Rollback Tran TR1
End
End
Else
Begin
Rollback Tran TR1
End
set nocount off
-
thank you very much Sreejith Sreedharan. I want to use rollback and commit but i have no idea how to use them. Until I saw your example. Thank you for sharing!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply