Sometime we want to compare the records between two database tables. These databases could be on same server or on different servers. Here is some information about how we can compare records between two tables. Hope this will help and give you some information.

 

Compare Two table records using EXCEPT operator

 

The ‘EXCEPT’ operator performs the compare between two tables. Read this MSDN article to know about Except operator

 

Compare records when both databases are on same server:

The following query returns any distinct values from the query to the left of the EXCEPT operand that are not also found on the right query.

SELECT ProductID

FROM Production.Product

EXCEPT

SELECT ProductID

FROM Production.WorkOrder ;

–Result: 266 Rows (products without work orders)

The following query returns any distinct values from the query to the left of the EXCEPT operand that are not also found on the right query. The tables are reversed from the previous example.

SELECT ProductID

FROM Production.WorkOrder

EXCEPT

SELECT ProductID

FROM Production.Product ;

–Result: 0 Rows (work orders without products)

 

Compare records When one database is on another server (remote server):

Click here to know about how to fire sql query to the remote server.

1^st link to the remote server. Use sp_addlinkedserver SP as given below

EXEC sp_addlinkedserver

@server = ‘SourceServer’

, @Srvproduct = ”

, @Provider = ‘SQLNCLI’ // SQL Server provider name

, @datasrc = ‘Remote server’ // remote SQL server instance name

 

Now run the same query given above to compare. Note: here you need to provide linked server name with the query – see below example

 

SELECT ProductID

FROM Production.Product

EXCEPT

SELECT ProductID

FROM SourceServer.Production.dbo.WorkOrder ;

–Result: 266 Rows (products without work orders)

 

Don’t forget to remove linked server once you done with comparison. Use sp_dropserver stored procedure to remove:

EXEC sp_dropserver ‘SourceServer’, ‘droplogins’;

 

More information can be found @ http://msdn.microsoft.com/en-us/library/ms188055.aspx

Comments are closed