Friday, 20 March 2009

Transaction management in SQL Server 2005

This is an example of how to use try/catch functionality in SQL Server 2005 to manage transactions.

USE [MyDatabase]

PRINT 'Moving Data'
GO

BEGIN TRY
  BEGIN TRAN

    PRINT 'Inserting..'
    INSERT INTO table2(Id,ParentId,Name)
    SELECT t1.Id, t1.ParentId, t1.Name
    FROM Table1 t1
    WHERE t1.ParentId = 1

    PRINT 'Deleting..'
    DELETE FROM [Table1] i
    WHERE t1.ParentId = 1    

  COMMIT TRAN

END TRY
BEGIN CATCH
 
  ROLLBACK TRAN 
 
END CATCH 
GO

No comments:

Post a Comment