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.
Advertisement
Answer
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.