Clever Geek Handbook

Revert changes to a table in a database

my application has the following procedure.

there is a product database (20,000 rows).

our client has an "import" function where he imports an excel file.

this is accomplished by deleting all the rows of the product table and then performing the import - which is very important since we are doing the data calculations programmatically.

the obvious problem is that the "import" action fails (IO stuff), now they don't have any / partial / curropt data in the product table.

We want the import operation to fail, leaving the original data.

this is an ASP.NET application written in C # using SQL Server 2005 and using XSD that we have built with VS2005 design tools.

+1


source to share


4 answers


  • Start a transaction
  • delete data from table
  • Insert new data
  • If the problem does not occur, commit the changes


+4


source


I would import the data into a table with the same structure as your products table and then replace the data in your products table as soon as you are happy that the import was successful.



This means that users can continue to use the system during import, and also minimize downtime when updating the product table.

+1


source


Using a transaction would be the obvious choice here.

I think the first thing I would like to ask is, do you really need to clear the entire table? Is there a timestamp or something you can use to limit the amount of data that needs to be updated? Could you reuse logic to use updates instead of all deletes and inserts? This way your transactions will be smaller.

Dan

+1


source


I would go with a transactional approach as described above. But the only problem I see is that you can lock the entire table for the entire period of the import process. you might have to think about it. A split table approach might be one solution.

+1


source






More articles:


All Articles