Getting Only What Changed from SQL Server 2005 We have a 17 store system which are on SQL Server 2005 Standard Edition and on Windows 2003 server. We currently have the following process:


At each store we have an SQL job which runs a procedure to populate 1 table. Procedure basically first Truncates the table and then does the Insert from the system. This has records from day our store was open till now, approximately 2million records in this table. There is no way to archive it.
We then have an SSIS package which gets this table data from store's back to Home Office every night. It runs for 4-7hours depending upon connectivity, which is slow most of the time.


We would like to do the following:


We would want to create a separate table at the store, which would record ONLY the changes occurred to table its populating every night (above Step-1)
This way, when SSIS package pull the data, it doesn't have to pull back 2million records, instead it can only pull back the changes


Due to SQL Server 2005 we cannot use MERGE command and as table gets Truncated and Inserted is not the good candiate for replication. How should we get ONLY change back to the home office?

Thank you,
Hiren Patel
I would change the process to be more like the following.  Not the best solution, but there aren't that many good options to begin with.  

Import Data Process:
a) - Drop yesterday's table
b) - rename current table to *_yesterday
c) - Import from system to current

Create view in Store's SQL Server that will return only rows that are new/different between current and _yesterday's tables. Something like the below.

SELECT Current.*
FROM Current
LEFT OUTER JOIN Yesterday ON Yesterday.PK = Current.PK (or list each field out seperately)
WHERE Yesterday.PK IS NULL (so the new ones)
OR ISNULL(Yesterday.Field1, '') &lt;&gt; ISNULL(Current.Field1, '')
&lt;repeat&gt;


2) - Move data to HQ
a) - Use view created to just pull across only the new records (possibly into staging table).  Then use merge? (is HQ a higher version of SQL?) to push the newly updated data into HQ's copy of the data.