Merging Tables

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

Home >  Tutorials > "ZooDB" Database > Comparing Database Data >

Merging Tables

Let's pretend that one of our recently hired zookeepers, Bruno Katz, has a degree on veterinary medicine and now joins the team of veterinarians of our tutorial zoo. In this step of the tutorial, you will transfer Bruno's record from tblZookeepers to tblVeterinarians using DatabaseSpy's merging functionality.

 

 

To transfer a record set to another table:

1.Click the Data Comparison ic_data-comparison button in the Standard toolbar to open a new Data Comparison window.
2.In the Select Database Objects for Comparison dialog box, choose "ZooDBConnect" in the Data Source drop-down list, and activate the tblVeterinarians and tblZookeepers check boxes in the Source group box.
3.Click OK. Both tables are added to the left comparison component; the right component remains empty with no data source assigned.
4.Click dbo.tblZookeepers and, keeping the mouse button pressed, drag the table into the right component. Note that both components now have the same data source assigned.
5.Click the triangle next to dbo.tblVeterinarians in the left component and, keeping the mouse button pressed, draw a line to the triangle next to dbo.tblZookeepers in the right component. Release the mouse button when the shape of the cursor changes.

diff_mapping-manually

All columns except "VetID" and "ZookID" are also mapped automatically.

diff_vet-zook-mapped

6.In the same way, map column "VetID" with column "ZookID".
7.Click the Start Comparison ic_start-db-comparison button in the toolbar of the Data Comparison window.
8.Select the menu option Data Comparison | Show results.
9.In Data Comparison Result window, click the Find ic_find-diff button in the toolbar, enter "Katz" in the Find what field, and click Find next.
10.Close the Find dialog box and click the Merge data from right to left ic_merge-selected-right2left button in the toolbar. The Merge data (Right to Left) dialog box appears.

dlg_merge-r2l

11.Click the Show Merge script button and edit the merge script as follows (delete the first column "[VetID]", and the first value "15,"):

INSERT INTO [ZooDB].[dbo].[tblVeterinarians]([FirstName],[LastName],[Address],[City],[State],[Telephone],[DOB])VALUES('Bruno','Katz','87 Panther Place','Trenton','NJ','(515)555-7667','1969-09-13 00:00:00.000');

12.Click the Execute ic_execute-sql button or press F5.
13.In the Online Browser, select the ZooDB database in the ZooDBConnect data source and click the Refresh ic_refresh-datasource button in the Online Browser toolbar.
14.In the Data Comparison window, right-click tblVeterinarians and select SQL and Data | Retrieve data | All rows from the context menu. Bruno Katz has been added to the list of veterinarians.

result_vets_katz

15.In the Data Comparison window, right-click tblZookeepers and select SQL and Data | Edit data from the context menu.
16.Click OK in the message box that appears and select Bruno Katz in the result grid.
17.Click Delete row ic_delete-row in the toolbar and click the ic_commit button to save the change to the database.

© 2019 Altova GmbH