Writing a nightly process, which will query all inventory in our current CRM and dump all the data to Postgress 9.2 schema. The goal is to create a single system snapshot containing the latest records for reporting every night. The source data for some of the records may change during the course of the day. Since this is a complete data dump, it will be fairly large. I am deliberating which approach will be more efficient. Please also not that I am using the npgSQL data provider from Visual Studio 2013.
A.) Truncate all records in the schema and reload all records every night?
B.) Use the following technique to perform an Upsert? (http://www.the-art-of-web.com/sql/upsert/
Any other suggestions are also welcome...