Error handling with apply transactions

BHVS 61 Reputation points
2023-02-05T10:31:46.98+00:00

Hi All,

I have 10 tables, i want to take backup before load data in to main table.

If any error i want to track into separate table and apply transactions. if any table fails need to keep previous status.

Below steps following now:

step:1

If exists A_bak table drop It.

Step :2

select * into A_bak from A

step:3

delete from A

Step:4

insert into A

select * from c

I want to create a stored Procedure to error handle with transactions(using try and catch in sp).

Is there any better approach to do error handling apply transactions.

Thanks in Advance.

Azure SQL Database
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,757 questions

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Erland Sommarskog 101.4K Reputation points MVP
    2023-02-05T11:06:30.14+00:00

    Why not wrap the whole operation in a single transaction, which will roll back if anything goes wrong?

    What you outline is certainly possible, but it has to count as advanced. If you don't do it right, there are many things that can go wrong. What is there is a failure during you roll-your-own rollback? This is something you need to take in account.

    The built-in mechanism in SQL Server is tested and tried. There is all reason to rely on it.

    1 person found this answer helpful.
    0 comments
  2. CosmogHong-MSFT 23,321 Reputation points Microsoft Vendor
    2023-02-06T02:33:00.82+00:00

    Hi @BHVS

    As Erland answered, you might try ROLLBACK TRANSACTION. Queries can be automatically or manually rolled back via transactions. Automatic rollback happens when a query fails to execute for any reason. Manual rollback occurs depending on user-defined conditions.

    Refer to this article for some samples: Rollback SQL: Rolling back transactions via the ROLLBACK SQL query

    As soon as you begin a transaction the table you perform the operations on gets locked. Locked tables cannot be edited so in order to keep tables speedy it’s very important that once a transaction is started it should commit or rollback as soon as possible.

    Best regards,

    Cosmog Hong

    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments