Requirement:
Compare two differently shaped data tables across two SQL Servers on a nightly basis.
Solution:
1. Create a view on each server that gets the data into a consistent (matching) shape. Use CAST to ensure the datatype also match:
2. Create a RedGate SQLDataCompare project that compares the two views:
3. Run a SQLDataCompare command nightly:
4. Which produces the following results:
In this case, 2 records were identical, two records were different and one record was found in "DB2" that didn't exist in "DB1".
Here is the summary and detail output, which is configurable from within your project file. I've opted to some identical records here, in production you probably wouldn't want to:
5. Email yourself the results
Compare two differently shaped data tables across two SQL Servers on a nightly basis.
Solution:
1. Create a view on each server that gets the data into a consistent (matching) shape. Use CAST to ensure the datatype also match:
2. Create a RedGate SQLDataCompare project that compares the two views:
3. Run a SQLDataCompare command nightly:
4. Which produces the following results:
In this case, 2 records were identical, two records were different and one record was found in "DB2" that didn't exist in "DB1".
Here is the summary and detail output, which is configurable from within your project file. I've opted to some identical records here, in production you probably wouldn't want to:
5. Email yourself the results
No comments:
Post a Comment