I want to write down a transaction for the following t-sql query so that if any of the statements fail it should rollback the whole stored procedure and do nothing.
Please find below the sample code:
As you can see in tbe attached Code.
If statment drop the table and then inserts data into the same table and after that there are 5 different insert statement.
I need a transaction so that if insert statement 3 failes it will roll back everything. So it will rollback insert statment 2 ,insert statement 1 and the drop table statment, same way if insert statement 4 fails than it should rollback everything prior to that and if no errors it should commit the transaction.
Thanks in advance.
IF EXISTS (select name from sysobjects where xtype='u' and name = 'tablename')
DROP TABLE [dbo].tablename
SELECT * INTO tablename
INSERT STATEMENT 1
INSERT STATEMENT 2
INSERT STATEMENT 3
INSERT STATEMENT 4
INSERT STATEMENT 5