What I need to do is update the crunch down data on a daily basis but I do not want to run the extract on a daily basis and replicate. I mean the easy way to do this would to set up a job that deletes the crunched data on the main server and reextract that on a daily basis then replicate the changes but that would replicate every inserted record on the table. Plus doing 450,000 delete would be transaction intensive since you can not truncate a table setup for replication. Also I would be replicating across a wireless network which would be a bit harsh as well.
So my idea is this, I set up a trigger that captures all insert, updates and deletes. I then write only the unique identifier to a holding table so I can tell which members has had something done to their account. I will also put a column in this table called uprec and default it to 0. Then after the nightly batch is ran I will run a stored proc that will query the holding table and select each distinct unique id and uprec = 0. Crunch the data to the application table by updating or inserting the new records or changed records then mark the uprec value from 0 to 1 for all the entries for that unique id in the holding table. Then run replication on the table to the application server across the wireless. Then delete from the holding table all records with a uprec of 1.
Any other suggestions?
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
