MSSQL – Delta Records

Scenario: You have to tables with similar information and you need to see what is different

Say Table1 & Table2 are two sales contact tables and you want to combine the tables

See SQLFiddle for the data schema to allow you to test the queries below.

In each example it is assumed that Table2 is more recent than Table1

First Example
This first example will show a difference or delta of the two tables.
This example only accounts for the first name and last name columns

SELECT *
FROM Table2 AS n
WHERE NOT EXISTS (
		SELECT *
		FROM Table1 AS o
		WHERE n.firstname = o.firstname
			AND n.lastname = o.lastname
		)

If you simply run the above query you will only see three new columns but if one of the other columns is different then you wouldn’t see the delta.

Second Example
This example will take all columns (other than id) into account and show the delta

SELECT *
FROM Table2 AS n
WHERE NOT EXISTS (
		SELECT *
		FROM Table1 AS o
		WHERE n.firstname = o.firstname
			AND n.middlename = o.middlename
			AND n.lastname = o.lastname
			AND n.phone = o.phone
			AND n.email = o.phone
		)

In this example you should see that the middle name for id 1 was updated to not only be an initial.

Third Example
We will utilize code on this page

First we will update Table1 with the differences.

UPDATE Table1
SET Table1.firstname = Table2.firstname,
	Table1.middlename = Table2.middlename,
	Table1.lastname = Table2.lastname,
	Table1.phone = Table2.phone,
	Table1.email = Table2.email
FROM Table1
INNER JOIN Table2 ON Table1.id = Table2.id
WHERE Table1.firstname <> Table2.firstname
	OR Table1.middlename <> Table2.middlename
	OR Table1.lastname <> Table2.lastname
	OR Table1.phone <> Table2.phone
	OR Table1.email <> Table2.email

As we have updated the records that existed we now update the WHERE statement to look for delta items based on id

INSERT INTO Table1 (
	firstname,
	middlename,
	lastname,
	phone,
	email
	)
SELECT firstname,
	middlename,
	lastname,
	phone,
	email
FROM Table2 AS n
WHERE NOT EXISTS (
		SELECT firstname,
			middlename,
			lastname,
			phone,
			email
		FROM Table1 AS o
		WHERE n.id = o.id
		)

This query should insert the three new records

Should you not be able to access SQLFiddle or the schema changed this is the original schema

IF OBJECT_ID('Table1', 'U') IS NOT NULL DROP TABLE Table1;
IF OBJECT_ID('Table2', 'U') IS NOT NULL DROP TABLE Table2;

CREATE TABLE Table1 (
	id INT IDENTITY(1, 1) NOT NULL,
	firstname NVARCHAR(35) NULL,
	middlename NVARCHAR(35) NULL,
	lastname NVARCHAR(35) NULL,
	phone NVARCHAR(35) NULL,
	email NVARCHAR(35) NULL
	);

CREATE TABLE Table2 (
	id INT IDENTITY(1, 1) NOT NULL,
	firstname NVARCHAR(35) NULL,
	middlename NVARCHAR(35) NULL,
	lastname NVARCHAR(35) NULL,
	phone NVARCHAR(35) NULL,
	email NVARCHAR(35) NULL
	);

INSERT INTO Table1
VALUES (
	'John',
	'D',
	'Doe',
	'800-800-8000',
	'email1@null.com'
	);

INSERT INTO Table1
VALUES (
	'Jane',
	'R',
	'Doe',
	'800-800-8001',
	'email2@null.com'
	);

INSERT INTO Table1
VALUES (
	'George',
	'P',
	'Done',
	'800-800-8002',
	'email3@null.com'
	);

INSERT INTO Table1
VALUES (
	'Georgia',
	'D',
	'Done',
	'800-800-8003',
	'email4@null.com'
	);

INSERT INTO Table1
VALUES (
	'Jordan',
	'Q',
	'Dnoe',
	'800-800-8004',
	'email5@null.com'
	);

INSERT INTO Table2
VALUES (
	'John',
	'David',
	'Doe',
	'800-800-8000',
	'noemail1@null.com'
	);

INSERT INTO Table2
VALUES (
	'Jane',
	'R',
	'Doe',
	'800-800-8001',
	'email2@null.com'
	);

INSERT INTO Table2
VALUES (
	'George',
	'P',
	'Done',
	'800-800-8002',
	'email3@null.com'
	);

INSERT INTO Table2
VALUES (
	'Georgia',
	'D',
	'Done',
	'800-800-8003',
	'email4@null.com'
	);

INSERT INTO Table2
VALUES (
	'Jordan',
	'Q',
	'Dnoe',
	'800-800-8004',
	'email5@null.com'
	);

INSERT INTO Table2
VALUES (
	'Jackson',
	'T',
	'Dnoe',
	'800-800-8005',
	'email6@null.com'
	);

INSERT INTO Table2
VALUES (
	'Justin',
	'E',
	'Arretta-Doe',
	'800-800-8006',
	'email7@null.com'
	);

INSERT INTO Table2
VALUES (
	'Jackie',
	'Z',
	'Arretta-Doe',
	'800-800-8007',
	'email8@null.com'
	);