Merging Schemas

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

Home >  Tutorials > "ZooDB" Database > Comparing Database Schemas >

Merging Schemas

After you have started a comparison and examined the differences between the schemas, you can merge the two schemas. Since changes to the database structure cannot be easily undone, DatabaseSpy will not directly perform a merge but display the merge script in an SQL Editor window where you can review the script and execute it if you are sure that the changes to the schema structure should be committed to the database.

 

You can either merge all differences at once (i.e., create one merge script that contains all the changes to the schema structure) or choose one or more particular differences to merge (i.e., create a merge script that contains selected changes). The merge itself can be performed in both directions, that is, from left to right or from right to left. You can therefore also decide to mix the schema structure by merging some changes from left to right and others from right to left.

 

The screenshot below shows the merge script for copying the structure of schema "dbo" to schema "test" (merge left to right).

merge_l2r

A different merge script is generated if you decide to merge the changes from right to left:

merge_r2l

In this section of the tutorial, you will rename test.tblVets to test.tblVeterinarians (merge left to right) and add the e-mail column to both dbo.tblZookeepers (merge right to left) and test.tblVeterinarians (merge left to right).

 

 

To merge schemas in the zoo database:

1.Select the menu option Schema Comparison | Collapse items or right-click the title bar of either component and choose Collapse items from the context menu.
2.Expand database and schema and select either dbo.tblVeterinarians or test.tblVets.
3.Select the menu option Schema Comparison | Show merge script: Left to Right or right-click the table in the component and choose Show merge script: Left to Right from the context menu. The merge script is displayed in a new SQL Editor window.

merge_tblVets

Note that the script contains only SQL for renaming the table itself; remember that there were also two columns with different names in this table. To generate a merge script that not only renames the table but also the two columns, you have to select the columns as well before you generate the merge script. Close the SQL Editor window without executing or saving the script, and get back to the Schema Comparison window.

 

4.Expand tblVeterinarians and select the table name as well as columns "LastName", "Telephone", and "Email" (hold down the Ctrl key to select multiple items).

merge_select-cols

5.Select the menu option Schema Comparison | Show merge script: Left to Right and check the merge script in the SQL Editor.

merge_tblVets-complete

Now both table and columns will be renamed and the column "Email" will be added to schema test.

6.Make sure that the SQL Editor is still connected to ZooDB and click Execute ic_execute-sql.
7.In the Online Browser, click the Refresh ic_refresh-datasource button in the toolbar or right-click ZooDB and select Refresh from the context menu. Note that dbo.tblVeterinarians now appears unmapped in the Schema Comparison window.

merge_tblVets-unmapped

8.Double-click the title bar of the right comparison component and select the renamed table tblVeterinarians in the Select Database Objects for Comparison dialog box.
9.Click the Compare ic_start-db-comparison button in the comparison window toolbar. No differences now exist between dbo.tblVeterinarians and test.tblVeterinarians.
10.Expand test.tblZookeepers, right-click column Email and select Show merge script: Right to Left from the context menu.

merge_tblZook-r2l

11.Execute the merge script, refresh the database, and run a comparison to see the changes in the Schema Comparison window. No differences now exist between dbo.tblZookeepers and test.tblZookeepers.

© 2019 Altova GmbH