January 15, 2010 at 4:00 pm
hi,
forexample i have a stock table on mobile device sqlce which got from an sql 2000 database by a select query, i need to get only the changed rows on sql2000 table to my mobile device. maybe the price changed, or stock quantity or maybe a new stock inserted. i don't want to delete table on sqlce and get all data from sql 2000 again.How may i do it??
January 15, 2010 at 5:57 pm
Hi there,
I would create a DateTime NOT NULL column, ChangeDate on the Sql 2000 table with a getdate() default, and update this column, with a trigger whenever an update is performed in the record.
On the SqlCe side i would have also this column, so whenever the SqlCe needs to check for changes, it could:
1- get de MAX(ChangeDate) from the SqlCe table
2- get records from Sql 2000 that where modified after that date
José Cruz
January 16, 2010 at 11:00 am
If you don't need to know the time the record changed, you just need to know *that* it changed, a timestamp column will serve the same purpose without the trigger.
January 16, 2010 at 11:17 am
Even that may not do it... you can update a row with the same data as what it started with and the TimeStamp will still change.
--Jeff Moden
January 16, 2010 at 12:06 pm
This is true. It will depend on your actual situation as to which is the more efficient method. It might be that copying a few extra rows that haven't *really* changed, only had the same data re-written, is a less expensive option than checking the status of every field against what it used to be.
I made a guess based on the synching to a mobile device that a timestamp check would be the cheaper option; as if you want to check every field you have to actually transmit the entire row one way or the other to check the values and see what you actually need to copy. That's potentially a lot of extra traffic.
On the other hand, if the bandwidth isn't as big an issue as the storage space or making sure you have only the rows that have truly new information, checking every field is the way to go. I guess the question is, what defines a change? Is it when either the mobile device or the server has changed and the two are no longer equivalent?
You could also have triggers on both databases that checked for when a value was changed and then update a field that was checked for differences between the two, but you're still looking at having to do an additional comparison to see if the same field was changed on both databases to be the same value, at which point the row is no longer a 'changed row'.
January 16, 2010 at 12:37 pm
The original post seems to suggest that this is all for a staging table upsert into a main table (or maybe I've not had enough coffee). I'd recommend a simple multicolumn inner join to mark (as a scheduled job) all of those things in the staging table that are different. It's a single pass, no need of triggers, etc. If the target table has a small PK (1 or just a couple of columns), then the "mark" should be with an entry from the PK to make the join for the update very easy. Anything not marked in such a fashion must be "new" and in need of an INSERT with no join.
--Jeff Moden
January 17, 2010 at 12:29 pm
I just re-read it and I think you're right. *I'm* working with 2 way synchronization between a mobile device/server so I think I jumped to that conclusion a bit too quickly =).
January 17, 2010 at 2:27 pm
thank's for the answers, i tried updated_date_time column also there was in the table and that's okay. But i wished that may be there was a word for the SELECT query about this issue. Because i would guess that SQL Database Log file is saving the transactions and if it saves we may Know the last updates on the tables.
January 17, 2010 at 9:11 pm
Why would the log file know anything about the relationships between a staging table and a primary table?
--Jeff Moden
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply