Pages

How to use Tablediff Utility?


SQL Server comes with many command line tools and one new tool that is available is the tablediff command. This tool help us to perform tasks:

1. Compare the two tables.
2. Tell us where any differences occur in the data between two tables.
3. Generate T-SQL commands to get the tables back in synch.

Note: SQL codes are not executed, it will just be written to a file for us to execute if we wish.

The command takes a few basic parameters to run.

   sourceserver
   sourcedatabase
   sourcetable
   destinationserver
   destinationdatabase
   destinationtable

Here is an example command that compares two tables Table1 and Table2 in the same database.

Lauch the command prompt and navigate to direcotry where tablediff executable file resides

I am using the Microsoft SQL Server 2012

C:\Program Files\Microsoft SQL Server\110\COM

execute the command

tablediff.exe -sourceserver MARKDEV  -sourcedatabase LCMS_DEV -sourcetable employees -destinationserver MARKUAT -destinationdatabase LCMS_UAT -destinationtable employee -et Difference -f c:\employee_differences.sql

Arguments

-et Use this argument to see the differences in a table.  The "et" parameter will create a table, in our case called "Difference", so we can see the differences in a table.
-f use this argument that will create a T-SQL script to synchronize the two tables.

How to add the computed column to a table based on the columns already exist in the table?

The application contains a table that has the following definition:

CREATE TABLE Items
(ItemID int identity(1,1) NOT NULL PRIMARY KEY,
ItemsInStore int NOT NULL,
ItemsInWarehouse int NOT NULL)

You need to create a computed column that returns the sum total of the ItemsInStore and ItemsInWarehouse values for each row.

ItemID ItemsInStore ItemsInWarehouse
1 30 35
2 400 50
3 11 3

Alter table Items
Add TotalItems AS ItemsInStore + ItemsInWarehouse


ItemID ItemsInStore ItemsInWarehouse TotalItems
1 30 35 65
2 400 50 450
3 11 3 14