5

Resolved

Table data diff

description

An option to diff the data of two tables in two different databases and the generation of Updates and Inserts would help, too. Basically it would diff the data based on the tables primary key.
New rows generate an INSERT, existing rows generate an UPDATE if the rows differ.

file attachments

comments

ErikEJ wrote Feb 25, 2012 at 11:44 AM

Thanks for the suggestions, the Data diff sounds like a great idea. But I am not sure I understand the point of the UPDATE statements - could you provide an example use case or some sample script. Normally, you would only update a few rows, or maybe I misunderstand?

Lord_Helmchen wrote Feb 26, 2012 at 6:33 PM

In my case it is that the data generation/collection is done by others and the data structure is done by me or a few other developers. It's a noncommercial software that keeps user data in the database next to data generated by us. The update statements would be to update the data in the user databases via script (on program start) when they run a new version.

ErikEJ wrote Apr 15, 2012 at 12:00 PM

Could you provide a sample for the UPDATE case, still having trouble understanding it.

Lord_Helmchen wrote Apr 23, 2012 at 5:30 PM

Here is a sample. Basically it would diff the data based on the tables primary key.
New rows generate an INSERT, existing rows generate an UPDATE if the rows differ.

ErikEJ wrote May 1, 2012 at 7:25 AM

Thanks for the sample, assume that is related to the DIFF case. My question was related to the option to generate data Update statements by primary key on right click - I have trouble understanding the purpose of this?

Lord_Helmchen wrote May 2, 2012 at 6:27 PM

Oh I was just clarifying, that the comparison, when doing a data diff, of the different tables in the two databases should work by primary key.

"I have trouble understanding the purpose of this"
I don't see a reason to generate updates on right click, because the data diff shall do just that, but better.
So it is basically just a misunderstanding.

ErikEJ wrote May 3, 2012 at 6:29 AM

Thanks for the clarification. Can I assume (for the initial release, anyway), that both tabels particpating in the diff process will have same structure?

Lord_Helmchen wrote May 3, 2012 at 9:48 AM

For the initial release assuming the structure is identical is fine.
later I would only assume that the primary keys have the same structure, because otherwise a comparison is in vain.
And then compare just all fields, that exists in both tables with the same/a similar structure.

Lord_Helmchen wrote Sep 24, 2012 at 6:00 PM

I did some coding for this issue. It's not done, yet. I was not sure what helper methods are available in your code to make things easier.

ErikEJ wrote Sep 25, 2012 at 10:25 AM

YOu should hava a look at CreateDataDiffScript in the exportsqlce project, that is where your code belongs, I have started a sekelton method here: http://exportsqlce.codeplex.com/SourceControl/changeset/view/93638#1698085

ErikEJ wrote Apr 22 at 5:30 PM

Sorry, I just relalized today, that you have done a major contribution - I will see to that this gets included in the NeXT release (at least as a beta feature)....

wrote May 20 at 11:18 AM

Associated with changeset 84809.