Merging Database Differences

www.altova.com Print this Topic Previous Page Up One Level Next page

Home >  Comparing Database Data >

Merging Database Differences

After you run a data comparison (see Running a Database Data Comparison), you can optionally merge any differences either from the left to the right database, or vice versa. Differences can be merged either in batch for the entire database, or individually at table or even row level.

 

You can execute the merge operation either directly, or through a merge script. The merge script can be generated at database, table, or row level. In addition to the merge script, you can also generate a restore script which can be used to undo the effect of a merge. The restore script can be generated at database or table level.

 

By default, database transactions as well as rollback on error is enabled on data merging (assuming the database supports this), see Database Data Comparison Options.

 

Note:It is not possible to merge columns that have incompatible data type (for example, string to numeric type). Also, sometimes a merge is possible only in one direction (for example, a numeric type may be converted to a string type, but not the opposite).

 

DatabaseSpy will ignore incompatible merges and will notify you, before merging, if such occurrences exist. In the Comparison Result view, merges that are only possible only in one direction are marked with a small red arrow joined with a green arrow. The direction of the green arrow indicates the direction where the merge is possible. For example, in the image below, the description column (of type "text") cannot be mapped to the quantity column because the latter is numeric. However, the quantity column can be mapped to the description column.

dbdiff_compare_data_09

Comparison Result View

 

Generating the merge script

The merge script can be generated for either the "left" or "right" database component, as follows:

 

1.Run a database data comparison (see Running a Database Data Comparison).
2.Do one of the following:

 

To generate the script at database level, right-click an empty area in the Comparison window and select ic_merge-selected-left2right Merge all Left to Right.
To generate the script at table level, right-click the table in the Comparison window and select ic_merge-selected-left2right Merge Selected Left to Right.
To generate the script at row level, right-click the row in the Comparison Result View and select ic_merge-selected-left2right Merge data from left to right.

dbdiff_compare_data_10

3.Click Show merge script.

 

Note:The commands above assume you want to merge the changes from left to right. Otherwise, use the corresponding reverse (right to left) commands.

 

Generating the restore script

If you would like to be able to undo the effects of the merge script, a restore script must be generated. The restore script can be generated for either the "left" or "right" database component, as follows:

 

1.Run a database data comparison (see Running a Database Data Comparison).
2.Do one of the following:

 

To generate the script at database level, right-click an empty area in the Comparison window and select SQL and Data | Show restore script (left or right, as applicable).
To generate the script at table level, right-click the table in the Comparison window and select SQL and Data | Show restore script (left or right, as applicable).

 

Executing the merge directly

1.Run a database data comparison (see Running a Database Data Comparison).
2.Do one of the following:

 

To merge differences at database level, right-click an empty area in the Comparison window and select Merge Left to Right.
To merge differences at table level, right-click the table in the Comparison window and select Merge Selected Left to Right.
To merge differences at row level, right-click the row in the Comparison Result View and select ic_merge-selected-left2right Merge data from left to right.

dbdiff_compare_data_11

 

3.Click Yes.

 

Note:The commands above assume you want to merge the changes from left to right. Otherwise, use the corresponding reverse (right to left) commands.

 

Reverting a merge

To revert a merge, run the restore script against the database (see above for instructions about generating the restore script).


© 2019 Altova GmbH