Compare 2 datasets and output delta in one TSQL statement

By Theo Ekelmans, 2011/11/04

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

Total article views: 1969 | Views in the last 30 days: 11

FORUM

Do not display "1900-01-01 00:00:00.000"

How to void displaying ''1900-01-01 00:00:00.000" if datetime data is null? where is that display...

FORUM

want only date from date-time format

want only 1980-12-17 instead of 1980-12-17 00:00:00.000

FORUM

datetime => how to have complete date with 00:00:00.000

Hi, i work with sql server 2005 and i 'm a beginner I have a table with a column datetime. ...

FORUM

2004-05-13 00:00:00.000 What is the datatype for this string?

Does anyone recognise the data type for the following string? 2004-05-13 00:00:00.000 If there...

FORUM

weired performance killing

Here is the part of the query that it taking too long (temp tables involved run in few seconds whic...

Compare 2 datasets and output delta in one TSQL statement

Do not display "1900-01-01 00:00:00.000"

want only date from date-time format

datetime => how to have complete date with 00:00:00.000

2004-05-13 00:00:00.000 What is the datatype for this string?

weired performance killing

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

You make SSC a better place

Compare 2 datasets and output delta in one TSQL statement

Do not display "1900-01-01 00:00:00.000"

want only date from date-time format

datetime => how to have complete date with 00:00:00.000

2004-05-13 00:00:00.000 What is the datatype for this string?

weired performance killing

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

You make SSC a better place

Already a member? Jump in: