Microsoft SQL Server 2008 R2 Unleashed- P17:SQL Server 2005 provided a number of significant new features and enhancements over what was available in SQL Server 2000. This is not too surprising considering there was a five-year gap between these major SQL Server 2008 is not as much of a quantum leap forward from SQL Server 2005 | 114 CHAPTER 5 SQL Server Command-Line Utilities User-specified agent parameter values -sourceserver local -sourcedatabase AdventureWorks2008R2 -sourceschema Person -sourcetable AddressType -destinationserver local -destinationdatabase AdventureWorks2008R2 -destinationschema Person -destinationtable AddressTypeCopy -f c TableDiff_Output Table AdventureWorks2008R2 . Person . AddressType on local and Table AdventureWorks2008R2 . Person . AddressTypeCopy on local have 2 differences. Fix SQL written to c . Err AddressTypelD Col Mismatch 1 Name Mismatch 5 ModifiedDate Name The requested operation took seconds. The output first displays a summary of the parameters used and then shows the comparison results. In this example it found the two differences that are due to updates performed on AddressTypeCopy. In addition the -f parameter used in the example caused the tablediff utility to output a SQL file that can be used to fix the differences in the destination table. The output file from this example looks as follows Host local Database AdventureWorks2008R2 Table Person . AddressTypeCopy SET IDENTITY_INSERT Person . AddressTypeCopy ON UPDATE Person . AddressTypeCopy SET Name Billing WHERE AddressTypelD 1 UPDATE Person . AddressTypeCopy SET ModifiedDate 2002-06-01 00 00 Name Shipping WHERE AddressTypelD 5 SET IDENTITY_INSERT Person . AddressTypeCopy OFF NOTE The tablediff utility requires the source table to have at least one primary key identity or ROWGUID column. This gives the utility a key that it can use to try to match a corresponding row in the destination table. If the -strict option is used the destination table must also have a primary key identity or ROWGUID column. Download from The bcp Command-Line Utility 115 Keep in mind that several different types of comparisons can be done with the tablediff utility. The -q option causes a quick comparison that compares only record counts and looks for differences in the
