Compare 2 datasets and output delta in one TSQL statement
It's a common problem we all share from time to time, and i thought my solution is worth a post 🙂
You have 2 datasets and you wonder..... has anything changed, and if so, did it change in A or B?
In my example i show a way to compare 2 sets of employee data and output the changes in one TSQL statement
Table A
| # | EmpNr | Name | HireDT | FireDT |
|---|
| 1 | 1 | janssen | 2000-01-01 00:00:00.000 | {NULL} |
| 2 | 2 | de bruin | 2001-05-01 00:00:00.000 | 2005-12-31 00:00:00.000 |
| 3 | 3 | pieterssen | 2004-08-01 00:00:00.000 | {NULL} |
| 4 | 4 | de vries | 2003-09-01 00:00:00.000 | 2011-01-01 00:00:00.000 |
| 5 | 5 | Klaassen | 2009-09-01 00:00:00.000 | 2011-01-01 00:00:00.000 |
| 6 | 6 | van Speyk | 2009-09-01 00:00:00.000 | {NULL} |
| 7 | 7 | Klaassen | 2009-09-01 00:00:00.000 | 2011-01-01 00:00:00.000 |
Table B
| # | EmpNr | Name | HireDT | FireDT |
|---|
| 1 | 2 | de bruin | 2001-05-01 00:00:00.000 | 2005-12-31 00:00:00.000 |
| 2 | 3 | pietersse | 2004-08-01 00:00:00.000 | {NULL} |
| 3 | 4 | de vries | 2003-09-01 00:00:00.000 | 2011-01-01 00:00:00.000 |
| 4 | 5 | Klaassen | 2009-09-01 00:00:00.000 | 2011-01-01 00:00:00.000 |
| 5 | 6 | van Speijk | 2009-09-01 00:00:00.000 | {NULL} |
| 6 | 7 | Klaassen | 2009-09-01 00:00:00.000 | 2011-01-01 00:00:00.000 |
| 7 | 8 | de groot | 2003-09-01 00:00:00.000 | 2011-01-01 00:00:00.000 |
The differences
| # | EmpNr_a | EmpNr_b | Name_a | Name_b | HireDT_a | HireDT_b | FireDT_a | FireDT_b |
|---|
| 1 | 1 | {NULL} | janssen | {NULL} | 2000-01-01 00:00:00.000 | {NULL} | {NULL} | {NULL} |
| 2 | 3 | 3 | pieterssen | pietersse | 2004-08-01 00:00:00.000 | 2004-08-01 00:00:00.000 | {NULL} | {NULL} |
| 3 | 6 | 6 | van Speyk | van Speijk | 2009-09-01 00:00:00.000 | 2009-09-01 00:00:00.000 | {NULL} | {NULL} |
| 4 | {NULL} | 8 | {NULL} | de groot | {NULL} | 2003-09-01 00:00:00.000 | {NULL} | 2011-01-01 00:00:00.000 |
As you can see, the output shows additions, removals and changes.
Happy Coding!
Theo Ekelmans
Sr MS-SQL DBA @ Ordina.nl
/*
this example will show you how to compare 2 datasets and output the differences in one statement
Author: Theo Ekelmans (theo@ekelmans.com)
Version: 1.0 2011-10-07
*/set nocount on
-- Original data
CREATE TABLE #tblA (
[EmpNr] [int], -- Primary key
[Name] [varchar](15) NULL,
[HireDT] [datetime] NULL,
[FireDT] [datetime] NULL,
CONSTRAINT [PK_tblA] PRIMARY KEY CLUSTERED ( [EmpNr] ASC )
)
-- Some copy you need to compare
CREATE TABLE #tblB (
[EmpNr] [int], -- Primary key
[Name] [varchar](15) NULL,
[HireDT] [datetime] NULL,
[FireDT] [datetime] NULL,
CONSTRAINT [PK_tblB] PRIMARY KEY CLUSTERED ( [EmpNr] ASC )
)
-- Create table to hold the Delta of tblA and tblB
CREATE TABLE #cmpAB (
[EmpNr_a] [varchar](10) NULL,
[EmpNr_b] [varchar](10) NULL,
[Name_a] [varchar](15) NULL,
[Name_b] [varchar](15) NULL,
[HireDT_a] [datetime] NULL,
[HireDT_b] [datetime] NULL,
[FireDT_a] [datetime] NULL,
[FireDT_b] [datetime] NULL
)
-- Fill tblA and tblB with some sample data (Epic Dutch names!)
INSERT INTO dbo.#tblA([EmpNr], [Name], [HireDT], [FireDT])
SELECT 1, N'janssen', '20000101 00:00:00.000', NULL UNION ALL
SELECT 2, N'de bruin', '20010501 00:00:00.000', '20051231 00:00:00.000' UNION ALL
SELECT 3, N'pieterssen', '20040801 00:00:00.000', NULL UNION ALL
SELECT 4, N'de vries', '20030901 00:00:00.000', '20110101 00:00:00.000' UNION ALL
SELECT 5, N'Klaassen', '20090901 00:00:00.000', '20110101 00:00:00.000' UNION ALL
SELECT 6, N'van Speyk', '20090901 00:00:00.000', null UNION ALL
SELECT 7, N'Klaassen', '20090901 00:00:00.000', '20110101 00:00:00.000'
INSERT INTO dbo.#tblB([EmpNr], [Name], [HireDT], [FireDT])
SELECT 2, N'de bruin', '20010501 00:00:00.000', '20051231 00:00:00.000' UNION ALL
SELECT 3, N'pietersse', '20040801 00:00:00.000', NULL UNION ALL -- n deleted
SELECT 4, N'de vries', '20030901 00:00:00.000', '20110101 00:00:00.000' UNION ALL
SELECT 5, N'Klaassen', '20090901 00:00:00.000', '20110101 00:00:00.000' UNION ALL
SELECT 6, N'van Speijk', '20090901 00:00:00.000', null UNION ALL -- y replaced by ij
SELECT 7, N'Klaassen', '20090901 00:00:00.000', '20110101 00:00:00.000' UNION ALL
SELECT 8, N'de groot', '20030901 00:00:00.000', '20110101 00:00:00.000'
-- Compare and insert delta into table, in one sql statement
Insert into #cmpAB
( [EmpNr_a], [EmpNr_b],
[Name_a], [Name_b],
[HireDT_a], [HireDT_b],
[FireDT_a], [FireDT_b]
)
select a.[EmpNr] as [EmpNr_a], b.[EmpNr] as [EmpNr_b],
a.[Name] as [Name_a], b.[Name] as [Name_b],
a.[HireDT] as [HireDT_a], b.[HireDT] as [HireDT_b],
a.[FireDT] as [FireDT_a], b.[FireDT] as [FireDT_b]
from (
-- Give all from B, if it's new or different
select [EmpNr], [Name], [HireDT], [FireDT]
from #tblB
except
select [EmpNr], [Name], [HireDT], [FireDT]
from #tblA
) b
full outer join -- Join the 2 delta sets
(
-- Give all from B, if it's an orphan or different
select [EmpNr], [Name], [HireDT], [FireDT]
from #tblA
except
select [EmpNr], [Name], [HireDT], [FireDT]
from #tblB
) a
on a.[EmpNr] = b.[EmpNr] -- Join on PK !
-- Show the tables
select * from #tblA
select * from #tblB
-- Show the Delta
select * from #cmpAB
-- Clean up
DROP TABLE #cmpAB
DROP TABLE #tblA
DROP TABLE #tblB
