Note the next change on the “Sales.SalesOrderDetail” table. Though we changed the record only in the “Sales.SalesOrderDetail”, a trigger attached to the table has updated both ‘Sales.SalesOrderHeader” and “Production.TransactionHistory” tables.
The changed will be applied to the target as “UPDATE”.
- Note that the “Write Updates” and “Export to Editor” buttons are enabled now. Click on the “Export to Editor” and analyze the script generated for the target database. The script contains;
DROP CONSTRAINT and CREATE CONSTRAINT statements for all foreign keys.
DELETE statement for Person.ContactType table.
UPDATE statement for Sales.SalesOrderDetail and Sales.SalesOrderHeader tables.
INSERT statement for Production.TransactionHistory table.
- Go back to the DataCompare tab and click on “Write Updates”. It updates the target database.
- Query tables and see whether updates are gone to target database.
Is everything in the target updated as the source? Note the message at the top of the DataCompare window. It says x number of items are compared and updated. To make sure that every change has gone, let’s compare again. Click on the “Refresh” icon in the “DataCompare” toolbar to start the comparison. Are all records identical? You will see that still data in the “Order” tables mismatched. Find the reason for the mismatch. The column “ModifiedDate” is the problematic column. Let’s update the target again. Click on the “Write Updates” to pass the correct updates from the source to target. Refresh and compare. Are records are still mismatched? Yes, this behavior continues because of the trigger attached to the “Sales.SalesOrderDetails” table. Once updates are received from the source, trigger gets fired at the target and updates the “ModifiedDate” column and “Sales.SalesOrderHeader” table. This default behavior can be stopped by enabling the “Disable triggers” option.
Data Compare Options
There are 7 Data Compare options available to change the behavior of the update process. Enabling and disabling allow you to pass the changes from source to target as you want. Options can be seen with “Main Options window (Tools -> Options)” under Database tools -> Data compare node. Here are the available options.
- Disable foreign keys
This is enabled by default. That is why we see drop and re-create of all foreign keys in the script. Enabling this will stop checking the foreign key availability at the execution, when records are updated and inserted but disabling this option enforce the checking. - Disable triggers
Enabling this stops firing triggers. If you had enabled this option before running the previous update, you would not see records mismatched. You can try above again by enabling this. - Drop primary keys, indexes and unique constrains
Once you enable the option, it suppose to drop all primary keys, all indexes, and unique constraints but I did not come across a script that drops primary keys and clustered indexes. Dropping indexes would be a good option if you update a table that is heavily indexed with large data set. - Include timestamp columns
The default setting for this option is “on”. When it is on, it compares the timestamp columns and shows if there are differences. But it does not update the target or generate script for update. Disabling the option stops comparing timestamp values. - Do not use transactions
Enabling this stops the script execution under a transaction. By default it is on. - Exclude IDENTITY columns
The default setting of this is “off”, hence it compares IDENTITY values. It does not compare IDENTITY values and show if there are differences when the setting is “on”. I found that it does compares IDENTITY even with the option “on” when the IDENTITY is a part of primary key. - Trim trailing
Enabling this option trims the trailing spaces in columns when the target is updated.
Hope I have given enough knowledge to you about the feature “Data Compare” and you will find it very useful. If you have not started using it, download the latest from here. Power tools for it can be downloaded from here.
Here are some of the references for Data Dude.
- Gert’s Data Dude Blog: http://blogs.msdn.com/gertd/
- Visual Studio Team Edition for DB Pros: http://blogs.msdn.com/camerons/
- Article I wrote about “Test Data Generation”: http://sqlserveruniverse.com/content/ADMN0100212262007DataGenerationwithDataDude.aspx
- Article I wrote about “Schema Comarison”:
http://sql-server-performance.com/articles/per/Schema_Comparison_with_VSTE_DBPro_p1.aspx
I would appreciate your comments on this, so please send your comments to dinesh@sqlserveruniverse.com.
]]>