Comparing data

Suppose you need to compare data coming from two tables.

ProductionServer

USE SampleDatabase

CREATE TABLE dbo.SameTable

(

 ID1 INT NOT NULL,

 ID2 INT NOT NULL,

 COL VARCHAR(50) NOT NULL,

 PRIMARY KEY (ID1, ID2)

)

INSERT INTO dbo.SameTable VALUES(1, 1, 'A')

INSERT INTO dbo.SameTable VALUES(1, 2, 'B')

INSERT INTO dbo.SameTable VALUES(1, 3, 'C')

TestServer

USE SampleDatabase

CREATE TABLE dbo.SameTable

(

 ID1 INT NOT NULL,

 ID2 INT NOT NULL,

 COL VARCHAR(50) NOT NULL,

 PRIMARY KEY (ID1, ID2)

)

INSERT INTO dbo.SameTable VALUES(1, 1, 'A')

INSERT INTO dbo.SameTable VALUES(1, 2, 'D')

INSERT INTO dbo.SameTable VALUES(1, 4, 'E')

From TestServer using the LinkProd linked server pointing to ProductionServer

we could use a full join query to spot the differences:

USE SampleDatabase

SELECT

 A.*,B.*

FROM

 dbo.SameTable A

  FULL JOIN

 LinkProd.SampleDatabase.dbo.SameTable B ON

  B.ID1 = A.ID1 AND

  B.ID2 = A.ID2

WHERE

 A.ID1 IS NULL OR

 B.ID1 IS NULL OR

 A.COL <> B.COL

You should obtain the follwing result:

ID1	ID2	COL	ID1	ID2	COL
1	2	D	1	2	B
NULL	NULL	NULL	1	3	C
1	4	E	NULL	NULL	NULL

Here you'll find the code to automatically create comparing scripts.

CompareData.zip