March 15, 2010 at 6:38 am
I have two tables. One is Master table and other one is sync data table. When the data uploaded from the mobile devices it will be saved in the sync data table. Once its saved there, I need to compare each column in the Master table and update the data if that data wasn't updated on the Master table from the last sync timestamp.
Is there any easy way to do that like a simple query or do I have to use the cursor etc. and do it manually?
Thank you and hope for the best suggestions,
vincy
March 15, 2010 at 7:16 am
If you do not need to do this more then once then I would do a data compare from SSIS.
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
March 15, 2010 at 7:33 am
It sounds like you have a pretty standard "upsert" situation there. (That's short for "update or insert".)
Most of those can be handled very simply by doing an update on the rows that have a match (usually on an ID column or something similar to that), and then inserting the rows that don't match. Two simple commands, no cursor, nothing complicated.
To get more specific, I'd need to see table structures and some sample rows (preferably in form of insert statements).
If you're actually using SQL 2000 (as per the forum this is posted in), a two-step solution is probably best. If it's SQL 2008 (sometimes people accidentally post in the wrong forum), then Merge can do this in one command.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 15, 2010 at 8:45 am
Oops! My bad! Didn't realize that its went under SQL 2000. This was my first post and apologize for the misplace. Is there any way to move it to 2008?
Anyway Yes, I am using SQL Server 2008. But the problem is, I have to check column by column because the Master tables are getting updated from the web frequently. and if the data is changed from the web from the last sync and also changed on the mobile device then I have to verify and make sure I did not screw up the data.
For example: Say I have table PatientInfo which has fields like Diagnoses and reason.
The Field Diagnoses is changed on the web, and the field reason is changed on the mobile device since the last sync to mobile.
Now, I have to update only the reason field and not the Diagnoses filed because only the reason field is changed on the mobile device. Is it possibile to do this with simple query, if I have the "Change Tracking" is enabled? Not sure how the Merge will work in this scenario, though.
March 16, 2010 at 6:35 am
I'm not familiar enough with the mobile version of SQL Server to really point you in the right direction on this.
What I'd do if it were Express on a laptop and Standard/Enterprise on a server, would either be "merge replication", or I'd define rules on the tables regarding a Merge command and then use that.
For example, you could have a "column version" column for each column you need to merge. Take the latest, and do exception handling on conflicts.
But I'm not sure how to implement those on mobile devices. I know merge replication is designed to handle the kind of situation you're describing, but it's not something I've set up for mobile.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 16, 2010 at 6:50 am
Thanks, at least I got the clue now. I can use the Merge Replication. I will read upon it and lets what it can do for me.
Also, we are not using mobile database at this time. What we have is persisted objects on the mobile device and the .net web services are taking care of data replication. So, once the web service received the data from the mobile device (can be any device, windows mobile, blackberry and/or iphone), it has to send that to the database by slicing and dicing the data and putting it on the right place in a right way.
Thought of using Microsoft Sync Framework but stupid Microsoft just says that Sync Framework can work with web services but doesn't even give a simple example on how can you achieve that. Most of the examples i find is for file sync and/or direct database sync but nothing for web services.
I have less time to investigate and more to do, so, decided to invent my own wheel and hang on there until I get some relaxation.
But thank you for all the time spend with this thread. But if you find any information on how to make Sync Framework with Web Services (seems like its using Merge Replication) just post here if you will.
Take care,
March 16, 2010 at 7:14 am
Merge replication is for when you have separate copies of parts of the database, so it won't work here for what you need.
I'd definitely look into the Merge command for this. You can define pretty complex rules on that pretty easily.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 16, 2010 at 8:42 am
Thanks again, I will definitely check out the Merge command. and will come back if I need any help. 🙂
July 29, 2011 at 3:29 am
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply