Post reply

How to wrap queries so if one fails they all fail

  • December 21, 2010 at 12:52 pm

    #229725

    I have a series of Update, delete, and select queries which I am using to set up default data in a data base. First I remove old set up data and delete from appropriate tables, then I insert my new data. Is there a way to wrap all of the data in one query and make it fail entirely if one part fails?

    The main issue I am having is that I delete from a couple tables and then try to delete from another that has a foreign key relations and get a foreign key error which cuts my query in half so that only half of it executes. I am using a table variable to temporarily hold data that needs to be deleted, and if the query fails half way through I lose what data I needed to delete. This leaves me with orphaned data that I have to go through table by table and delete.

    I know my query should be designed to take into account FK's but I can't test it as one entire script without the fear that I may have forgot something and hence caused myself a huge deal of work.

  • December 21, 2010 at 2:09 pm

    #1265276

    For your permanent and temporary tables (not table variables), you can wrap all of this up with a begin transaction statement, and with the use of try/catch you can decide whether to use a commit transaction or rollback transaction statement.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • December 21, 2010 at 3:15 pm

    #1265304

    WayneS (12/21/2010)

    For your permanent and temporary tables (not table variables), you can wrap all of this up with a begin transaction statement, and with the use of try/catch you can decide whether to use a commit transaction or rollback transaction statement.

    Cool that is what I was looking for, thanks a lot I will look into this.

  • December 21, 2010 at 3:18 pm

    #1265305

    Just incase anyone else sees this I found a link with an example that seems to describe this pretty well.

    TransactionLink <-- Click!

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply