Identity Column as PK or Unique Constraint (int column)
This column allowed us to see if a row in the source was new or already existed by comparing the source ID value with those that exist in the destination.
LastUpdateDate (DateTime Column)
This column was automatically updated by our application. If we found that our row was an existing row (based on the identity column value above) we were able to compare the source LastUpdateDate column with the DestinationUpdateDate column to see if the row's data changed since the last extraction.
To make things easier for us, we created a table that held the current Id value and current update date values for each table we extracted. This allowed for easy comparison during each load and it turned out that this table was also heavily reported against. (Reports contained the extract datetime so report viewers could clearly see how current the data was.)
If you do not have the luxury of having these columns present in your source you may have to key off of the source table's primary key. You may also have to perform comparisons of the columns that can potentially change.
Dig Deeper on SQL Server Interoperability
Related Q&A from Joe Toscano
Migrate DTS packages to SQL Server Integration Services with Migration Wizard and deploy SSIS packages in SQL 2005. Learn how to access DTS Designer ... Continue Reading
Can I call triggers in stored procedures or vice versa? Continue Reading
I have a table, let's call it AlarmTriggers and it has a Boolean field pulled into it. When my software writes to it the table doesn't set the field ... Continue Reading