Merging Database Schemas

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

Home >  Comparing Database Schemas >

Merging Database Schemas

After running a schema comparison between two database schemas (the "left" and the "right" one), table differences can be synchronized (merged) between the two databases. Since the merge operation affects the structure of the database, it is not applied directly. Instead, DatabaseSpy generates a merge script. The generated merge script can apply changes either from the "left" database to "right", or the other way round, from "right" to "left".

 

Note:The merge script synchronizes only differences resulting after comparing structure of tables (this includes changes to columns, keys, or constraints). Merging differences between other object types (such as stored procedures, functions, and views) is not supported.

 

Once the merge script is generated, you can choose to execute the merge script directly in DatabaseSpy,  or save it to a file for later execution.

 

Before executing the merge script against the database, it is always recommended to review it. If the structural differences between database schemas are numerous, the generated merge script will be equally big. To avoid a potentially dangerous update of multiple items in the target database through a big script, you could generate smaller scripts and apply them progressively. For example, you can select specific items (columns, constraints) that should be included in the merge, and then generate the script only for selected objects.

 

 

To generate and execute a merge script:

1.Run a database schema comparison (see Running a Database Schema Comparison).
2.Optionally, if you want to merge only specific items, select these items in a component using Ctrl+Click. Note that it has no influence on the direction of the merge whether you select the items in the left or in the right component (for example, you can select items in the right component and still choose to merge from left to right).

 

Important: Child elements of a table or column are not automatically included in the merge if you select just the parent. For this reason, when selecting a parent object (for example, a table), make sure to select also any child items that should be merged (such as columns or constraints). For example, it the column name is equal but the data type is different (as shown in the image below), you must select the data type item to create a valid merge script.

diff_select-child

 

3.Do one of the following:

 

On the Schema Comparison menu, select Show merge script: Left to Right.
Right-click the component, and select Show merge script: Left to Right from the context menu
Click the Merge selected from left to right ic_merge-selected-left2right toolbar button.

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

4.Review and edit the merge script as required, and click the Execute ic_execute-sql button to actually merge the schemas.

© 2019 Altova GmbH