Hello,
  Is there any easy way to compare to identical databases and
save/export the differences so i can import them ?

Thank you

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]

Michael,

Humm, reading your message again, I think you mean data, so no, i don't know the answer

--
Fernando Dias
[Team Elevate]

Dear Fernando,

> Do you mean data or the structure?

Data

Thank you

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

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.

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

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.