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, DiffDog 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 DiffDog, open it with DatabaseSpy for execution, 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 Diff and Merge menu, select Copy from Left to Right.
Click the Copy 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.

 

The SQL merge script is displayed in the "Merge Schema" dialog box. If no items have been selected before the merge was started (step 2 above), the Use all Items option is selected and the "global" merge script is displayed in the comparison window. The Use selected Items option is active if one or more items have been selected for merging. In this case, you can still decide to merge all items by selecting the Use all Items option.

dbdiff_schema_comparison_merge

4.Review the merge script, and click Execute.

 

If the merge script should be incorrect, you can cancel the operation, save the SQL merge script to a file and edit it with an external editor, or show and edit the SQL merge script in DatabaseSpy, if installed.

 

 

To execute the merge script in DatabaseSpy:

1.Right-click either a component header or one of the selected items, and select Show merge script: Left to Right in DatabaseSpy from the context menu (or, depending on the case, Show merge script: Right to Left in DatabaseSpy). This opens DatabaseSpy, creates a new project and adds a data source connection to the target database (that is, the database where the changes will occur). If a project is already open in DatabaseSpy, a new data source connection is added to the project and you may be asked to define a name for that data source. The merge script is displayed in an SQL Editor window, where you can check the script and edit it if required.
2.Connect to the data source, click the Execute ic_execute-sql button or press F5 to execute the change script and commit the schema changes to the database.

 

 

To save the merge script to a file:

Right-click either a component header or one of the selected items, and select Save merge script: Left to Right... from the context menu (or, depending on the case, Save merge script: Right to Left...).

© 2019 Altova GmbH