Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB General » View Thread |
Messages 1 to 8 of 8 total |
Compare databases and save differences ? |
Sun, Feb 5 2012 5:14 AM | Permanent Link |
Charalampos Michael | Hello,
Is there any easy way to compare to identical databases and save/export the differences so i can import them ? Thank you |
Sun, Feb 5 2012 6:14 AM | Permanent Link |
Fernando Dias Team Elevate | Michael,
Do you mean data or the structure? If its structure, EDB Manager can do that, it can generate a script that transforms the structure of a given database and makes it identical to a target database. -- Fernando Dias [Team Elevate] |
Sun, Feb 5 2012 6:17 AM | Permanent Link |
Fernando Dias Team Elevate | Michael,
Humm, reading your message again, I think you mean data, so no, i don't know the answer -- Fernando Dias [Team Elevate] |
Mon, Feb 6 2012 3:33 AM | Permanent Link |
Charalampos Michael | Dear Fernando,
> Do you mean data or the structure? Data Thank you |
Mon, Feb 6 2012 4:14 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Charalampos
If the two databases can be hooked onto the same session it should be possible to do it with SQL. Caveat: How easy depends on wether its whole records missing or just a field different. Roy Lambert |
Mon, Feb 6 2012 6:43 AM | Permanent Link |
Adam Brett Orixa Systems | Charalampos
As Roy says, if both databases can be linked to 1 session you can compare the existence of records. For example if your DB's have tables with the same names and structures, and a unique primary key on the "ID" field you can use SQL in the following form: CREATE TABLE T1T2differences AS SELECT * FROM T1 WHERE NOT T1.ID IN (SELECT T2.ID FROM T2) CREATE TABLE T2T1differences AS SELECT * FROM T2 WHERE NOT T2.ID IN (SELECT T1.ID FROM T1) This creates 2 tables of the records which differ between the 2 original tables. Essentially you have to start by running comparisons of this type for the whole DB ... once you have these "differences" tables you can then use them to generate updates INSERT INTO T1 (Field lists) SELECT Field lists FROM T1T2differences. Running the process in 2 steps gives you the chance to do some tests on the "differences" table if you wish to for example to check for conflicts in records. This system has a problem if the ID field is not a GUID or otherwise unique. If the ID is non-unique, then 2 records in T1 and T2 might share an ID but not be the same. In this case updates become far more problematic. -- If you want to monitor CHANGE differences between the two DBs you will have to start by creating some mechanism in the B to monitor change. This can take a number of forms: * Auditing table, which monitors every change i.e. have a trigger on every table in the DB to insert a row in the auditing table whenever an UPDATE or INSERT event occurs. * Auditing fields on every row in every table such as "DateEdited" (TIMESTAMP) If the DateEdited is updated everytime a user "touches" the record you have a mechanism to monitor which is the "youngest" data. Having this data recorded in the DB is then only the start ... you will have to write scripts to capture sets of updated data and then use these sets to reconcile the different versions of the database. -- EDB includes powerful features in PUBLISH DATABASE, SAVE UPDATES and LOAD UPDATES which allow automation of such processes for single databases with data stores distributed among different users ... but you can only use these if you have a "common starting point" of data from which to build up a mechanism for saving updates. -- I hope this is useful. |
Mon, Feb 6 2012 10:28 AM | Permanent Link |
Charalampos Michael | Dear Adam,
Thank you for the answer! > EDB includes powerful features in PUBLISH DATABASE, SAVE UPDATES and > LOAD UPDATES which allow automation of such processes for single > databases with data stores distributed among different users ... but > you can only use these if you have a "common starting point" of data > from which to build up a mechanism for saving updates. Well, can i use Load/Save updates to export/import or stream will be better ? Thank you |
Thu, Feb 9 2012 11:45 AM | Permanent Link |
Adam Brett Orixa Systems | The IMPORT / EXPORT SQL commands allow data from CSV or XML files to be "swapped" into or out of EDB table files.
This might be useful. The SAVE UPDATES / LOAD UPDATES SQL commands save specific "EDBUpd" format files which can be used to "bundle together" a whole set of data changes for 1 table. However you can only LOAD UPDATES into a table which perfectly matches the table the data came from ... you can't LOAD UPDATES into table B using an update file for table A. In your case (if I understand the problem right) I think you are going to have to use some SQL in the form: SELECT * FROM TableA WHERE NOT PrimaryKeyField IN ( SELECT PrimaryKeyField FROM TableB ) ... to detect differences between tables. and couple this with some mechanism (perhaps based on a field with a functionality of "TimeStampWhenEdited") to pick the most recently edited version of rows with matching PrimaryKeys. |
This web page was last updated on Sunday, May 5, 2024 at 10:18 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |