Ways to Compare and Find Differences for SQL Server Tables

Introduction

Sometimes, we need to compare 2 tables to see what was changed. This tip shows you 2 different ways to compare data.

Preparation

Let's create two tables with identical structure and add some data.

The code might be like this:

CREATE TABLE [dbo].[#1](
 [id] [nchar](10) NOT NULL,
 [type] [nchar](10) NULL,
 [cost] [money] NULL,)
GO
insert into [dbo].[#1] values
('001','1','40'),
('002','2','80'),
('003','3','120'),
('004','4','160'),
('004','5','160')
GO

CREATE TABLE [dbo].[#2](
 [id] [nchar](10) NOT NULL,
 [type] [nchar](10) NULL,
 [cost] [money] NULL,)
GO
insert into [dbo].[#2] values
('001','1','40.05'),
('002','2','80'),
('003','6','120'),
('004','4','160'),
('004','5','160.01')
GO

The easiest way to see the differences between two tables is using of except clause. Code could be like this:

SELECT ID, TYPE, COST FROM #1
EXCEPT
SELECT ID, TYPE, COST FROM #2

SELECT ID, TYPE, COST FROM #2
EXCEPT
SELECT ID, TYPE, COST FROM #1

Code below can show the differences in a more friendly format:

SELECT ID, TYPE, COST FROM
(
    SELECT ID, TYPE, COST FROM #1
    UNION ALL
    SELECT ID, TYPE, COST FROM #2
) A
GROUP BY ID, TYPE, COST
HAVING COUNT(*) <> 2

UNION ALL lets us combine all records from both tables. Condition 'HAVING COUNT(*) <> 2 hides identical records.