How to replicate different records with the same Ids

rgbunker

We have two laptops with the same schema. We use these laptops to collect data from different offsite locations and store them locally. We need a process to dump the data from individual laptops to the production database. If one of the tables on each of the laptops has a record with the same identifier then how can we replicate the changes to the production database?

Here is an example to give you an overall picture;

Laptop 1

Table name: SubjectAnswer
Primary Key: SubjectID
Fields with data values:
SubjectID, ChoiceID, AnswerText
1000, 345, 2/7/06

Laptop 2

Table name: SubjectAnswer
Primary Key: SubjectID
Fields with data values:
SubjectID, ChoiceID, AnswerText
1000, 566, 4/4/07

Production Server

Table name: SubjectAnswer
Primary Key: SubjectID
Fields with data values:
SubjectID, ChoiceID, AnswerText
1000, 343, 3/3/06

After running this tool we would like the production db to look somthing like this;

SubjectID, ChoiceID, AnswerText
1000, 343, 3/3/06 (production db)
xxxx, 345, 2/7/06 (laptop 1 db)
xxxx, 566, 4/4/07 (laptop 2 db)

Do you know if the sql data compare would solve this problem. The only other way I can think of is to use a GUID on each of the tables to prevent from overwriting the data values.


Thanks.

richardjm

There are a couple of ways you can work around this. Probably the best is to use an identity column for the SubjectID and have the seed be different on the different laptops ( say 1000 for laptop 1, 2000 for laptop 2, etc ).

The other thing you can use data compare to do is to use the ChoiceID and AnswerText ( assuming it's not a text column ) as the MatchingMappings again this assumes that your SubjectID is an identity column. Data compare will then notice missing data from the unique information that is the rest of the data. You should exclude the SubjectID from the comparison and synchronisation as it will get 'filled in' automatically by SQL Server.

Hope this helps and makes sense.

rgbunker

Assuming the records are unique across the publisher laptops and center subscriber server, do you have any c# sample code that I can use to move the data in the above tables from the laptops to the server programmatically using SQL Comparison and Synchronization Toolkit?

Thanks.