How to compare custom queries
dbForge Data Compare for MySQL allows you to compare the results of custom queries you get after query execution instead of comparing the entire tables or views.
There are two ways to compare custom queries using Data Compare:
Compare custom queries
1. On the toolbar, click New Data Comparison.
2. On the New Data Comparison wizard > Source and Target page that opens, select the source and target object type (a database or a scripts folder):
- For a Database type, select a connection and a database you want to compare.
- For a Scripts folder type, select a database scripts folder.
Note
You can create a new scripts folder by clicking
New and specify the details in the dialog that opens. For more information, see Compare and synchronize scripts folders.
3. Switch to the Mapping page and click Custom Query.
4. In the Custom Queries Mapping dialog, do the following and then click Map > Close:
- Select Query for the source and target objects
Note
If you select the Table or View option, you can map tables or views available on the list.
- Specify the name for the queries.
- In the SQL field, enter a query.
- Optional: Click Validate to verify that your queries work properly.
5. On the Mapping page, you may see the warning next to the query object: Objects to compare have no key column. It means that the custom comparison key is used but is not set. You should mark at least one column pair as a comparison key. To do that, in the Comparison Key column for the query you have added, click the dropdown arrow and select Custom.
6. In the Column Mapping dialog that opens, select the checkbox next to the column you want to set as a comparison key and click OK to save the changes.
7. To start the comparison, click Compare.
Note
The results of custom queries can be compared but cannot be synchronized. Generating the update script is also not possible.
Compare views
You can create views and then compare those views using dbForge Data Compare for SQL Server. Using this method, you can not only synchronize the views data but also obtain the update script.
To create a view, use the following syntax:
CREATE VIEW MyView
AS
SELECT Column1, Column2, ColumnN, ...
FROM TableName
WHERE Condition;
After the views have been created, you can compare them with Data Compare.
To compare views:
1. Initiate data comparison by clicking New Data Comparison on the toolbar.
2. The New Data Comparison wizard opens. On the Source and Target page, specify the connection details and click Next.
3. On the Options page, under Comparison Options select Compare views.
4. On the Mapping page of the comparison wizard, map the views you want to compare.
Note
When views share the same name, they are mapped automatically. Otherwise, you will need to map them manually.
5. Set a comparison key. For this, in the Comparison Key column for the view you want to add a key, click the dropdown arrow and select Custom. Then, in the Column Mapping dialog that opens, select the column to be used by Data Compare as a comparison key and click OK.
6. Click Compare.
7. In the comparison results document that will open, you can examine the differences and select objects for synchronization.
8. Click Synchronize. The Data Synchronization wizard will open. Follow its steps to configure the synchronization process and click Synchronize to deploy the changes.
Note
Not all views are updatable. Only views meeting the following conditions can be updated or synchronized:
The view should reference a single database table; it cannot reference multiple tables or employ complex expressions or joins.
The view’s SELECT statement must encompass all columns from the base table that are essential for the update operation, including primary key or unique constraint columns, which are necessary for identifying rows during updates and deletes.
The view must not incorporate any aggregate functions, DISTINCT, GROUP BY, UNION, or subqueries in the SELECT statement.
The view must not contain IDENTITY columns, computed columns, or columns with expressions.
When updating a view that is based upon another view, the latter should also be updatable.
None of the selected output fields in the view should use constants, strings, or value expressions.
Want to Find out More?
Overview
Request a demo
