SQL Server Portal

It is a very common scenario when you need to match a source table and a target table to find the missing (mismatch) rows across the result sets. We usually do it for multiple purposes specially at the time of audit or data migration etc.

Problem :
We usually use logical operators (NOT IN, Exists) to find the difference between two result sets. However, if you need to match each row by each row & each column by each column, it would be very difficult to achieve it via above mentioned logical operators.

Sample :
Let me create a sample to demonstrate the problem and solution.

--This script is compatible with SQL Server 2005
USE [AdventureWorks2012]
GO
--Create a sample table
CREATE TABLE [HumanResources].[Department_Sample](
[DepartmentID] [smallint] NOT NULL,
[Name] [dbo].[Name] NOT NULL,
[GroupName] [dbo].[Name] NOT NULL,
[ModifiedDate] [datetime] NOT NULL,
) ON [PRIMARY]
GO

--Insert records into the sample table from actual table
INSERT INTO [HumanResources].[Department_Sample]
SELECT * FROM [HumanResources].[Department]
GO

--Update a single record in the sample table
--to differentiate it from actual
UPDATE [HumanResources].[Department_Sample]
SET [Name] = 'Business Development'
WHERE DepartmentID=2

--Insert a record in the original table
INSERT INTO [HumanResources].[Department]
([Name]
,[GroupName]
,[ModifiedDate])
VALUES
('IT'
,'Executive General and Administration'
,GETDATE())
GO
--Delete a record from the sample table
DELETE FROM [HumanResources].[Department_Sample]
WHERE [DepartmentID]=11
GO
SELECT * FROM [HumanResources].[Department]
GO
--OUTPUT

Solution :
As mentioned above, I created a sample table, copied data from original table in it and then modified & inserted the data in the sample table & original table respectively. Now, in order to find the the missing (mismatched) records across the result set, we need to use EXCEPT operator. Given below is the script that will ONLY show the missing rows in the sample table (Target Table).

--This script is compatible with SQL Server 2005
USE AdventureWorks2012
GO
--Source Table
SELECT * FROM [HumanResources].[Department]
EXCEPT
--Target table
SELECT * FROM [HumanResources].[Department_Sample]
GO
--OUTPUT

Conclusion :
Whenever you need to find the missing (mismatch) row across the result sets, always use EXCEPT operator to make your life easier.

Read Full Post »