I have a database table with numerous fields including "category". The source of this table is an xml file which the system receiving occasionally. Each xml file contains data for one category. The table needs to be updated with the new category data from the xml file. The xml file includes all data for that category, not just the changes.
I see two possible ways of handling this:
First deleting all rows from the table where category = categoryID, and then doing inserts based on all the xml data. Obviously the deletion and insertion operations would be contained within a transaction.
For each record in the xml, first do a select to test if that record exists. If it does exist, update the data with that from the xml. If it doesn't exists, insert it.
Obviously, the second approach avoids deleting the data first but involves many more db queries although this could be mitigated by doing an initial select to a hash table, and just querying the hash. The big downside to the second approach is how to handle deletions, i.e. records that no longer appear in the xml and should be removed from the table.
What is the best practice for handling this kind of operation?
Thanks.
