0

Situation: a third party drops an XML file in a location, daily, where my windows app pick it up, drop the data from a table in my DB and replace it by the new data. Basically the XML represent the entire data not only the changes.

Question: instead of dropping the table every day, I would like to update my DB with only the updated data or new data, without going through every single row and compare them. Is there a way to do it in C#?

Thank you.

Peter Smith
  • 5,528
  • 8
  • 51
  • 77
Aurélien Ouédraogo
  • 15
  • 6
  • If you don't compare the new data with the existing data then how will you know what to update? – David Feb 13 '19 at 20:39
  • 1
    There are many ways to do it so the simple answer is yes. Can you show us what you have tried so far. SO is not a code writing service but is here to help and support with problems. – Peter Smith Feb 13 '19 at 20:41
  • True, that's why I was wondering if there is a less cumbersome way. – Aurélien Ouédraogo Feb 13 '19 at 20:41
  • sorry I don't have access to the code at the moment, but basically I just read the XML, deserialize it, drop my table and loop through my xml object and add each entry to the empty table – Aurélien Ouédraogo Feb 13 '19 at 20:44
  • Use merge statement [This](https://stackoverflow.com/questions/14806768/sql-merge-statement-to-update-data) will help you – NaDeR Star Feb 13 '19 at 20:45
  • If you are using C# and Entity Framework, and providing you have a key then it's straightforward to update/insert each record - if the key exists `update`, it not then `insert` – Peter Smith Feb 13 '19 at 20:50

1 Answers1

1

You don't have to drop the table. You can also truncate it, or delete all rows from it.

Dropping and truncating are fast operations but they actually change the data model (DDL statement) and are quite blunt. They basically throw away whatever data the database has for a table.

Deleting is an DML operation (modifying the data without touching the database structure). It has the potential benefit of being transacted, so you may rollback to the previous data if the import fails for whatever reason. But it may take longer because the database will keep a record of the changes made.

The statements are simple enough:

truncate TableName;

or

delete * from TableName;

If you really want to compare changes, you have to query the data first, or write a more complicated upsert statement, for which the exact solution may differ based on the database you're using. I doubt if that will be faster or more efficient, and I'd go for truncate or delete.

GolezTrol
  • 114,394
  • 18
  • 182
  • 210