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.
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.
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
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
Whenever you need to find the missing (mismatch) row across the result sets, always use EXCEPT operator to make your life easier.