Requires Free Membership to View
There are a few ways to interpret your situation, and without further information, it is difficult for me to suggest a specific strategy. So let me speculate a bit.
Suppose your "procedures" consist of huge and expensive extracts from many large or historic data tables, maybe on company databases that are sensitive to large queries. You do not want to run anything except the simplest procedures, and you do not want to run them more than once a month. If you tried to look for differences between last month and this month at the same time as doing the extract, you will incur more processing overhead against the company database than the simple extract.
So the first step is to decide whether you want to do a compare between your table and the company database, and usually the answer is no. Instead, you want to do a simple extract, and then compare your table to this extract. (Note: assembling several extracts from company tables might be better. In other words, run two extracts and join the results, rather than run a join and extract the results.)
The next step is to realize that you never want to extract all company data on a monthly basis. If the company database has a million products and you're only tracking ten thousand of them, you don't want to extract a million each month. So you have some filtering condition.
Lastly, you must extract all records within your filter, not just those which were added in the past month. The reason is that many were added more than a month ago. If you only extract the ones that were added in the past month, then how will you know when one of the older ones is deleted? It won't be there and you're just looking for the ones added in the last month.
So this whole problem boils down to ending up with two tables: yours and the monthly extract. And in most cases you can just throw yours away and rename the monthly extract to be your new table for the next month. You simply cannot beat this solution for simplicity, assuming you can afford the bandwidth and processing cycles to extract all ten thousand of your products each month. There's nothing more to do.
If you actually can detect deletes on the company side -- and additions, and changes -- in other words, if you can extract the actual transactions, efficiently, and there's only a couple hundred of them, then by all means capture those transactions instead of extracting your ten thousand products. Then you will run a series of queries to synchronize your table from these transactions, based on whether it's a new record, an update, or a delete. And those queries are simple to write. The tricky part is extracting delete transactions when the originals are gone from the company table.
This was first published in January 2005