Compare and update 2 tables

Question

Compare and update 2 tables

trinityr

SSChasing Mays

Points: 640
More actions
January 8, 2010 at 11:13 am

#219121
I am currently pulling data from Active Directory and our HR system.
The HR system will trump our AD db.
I need to compare the HR table against the AD table and output to email; the items that need to be updated on the AD table.
We will not be updating data on any tables. We simply want to comapre data and report differences.
Any ideas would be super!!!
I have read quite a bit on SELECT DISTINCT but am still not sure that is the route I need to take.
--Create AD_Users Table--
CREATE TABLE ActiveDirectory_Users
(displayNamenvarchar(255),
givenNamenvarchar(255),
mailnvarchar(255),
ObjectGUIDvarbinary(255) NOT NULL,
objectSidvarbinary(255) NOT NULL,
snnvarchar(255),
whenCreateddatetime);
ALTER TABLE ActiveDirectory_Users ADD CONSTRAINT pk_objectSid PRIMARY KEY (objectSid);
--Create HR_Users Table--
CREATE TABLE [dbo].[HR_Users](
[p_fname] [nvarchar](255) NULL,
[p_mi] [nvarchar](255) NULL,
[p_lname] [nvarchar](255) NULL,
[p_birth] [nvarchar](255) NULL,
[p_empemail] [nvarchar](255) NULL,
[p_jobtitle] [nvarchar](255) NULL,
) ON [PRIMARY];
The following columns contain common data
--Insert data into HR_Users
SELECT'Julie','K','Abrahamson','Aug 13 1959 12:00AM','julie.abrahamson@mydomain.com','Asst VP Compliance','Apr 20 1994 12:00AM', UNION ALL
SELECT'Philip','A','Albers','May 20 1960 12:00AM','phil.albers@mydomain.com','Assistant VP Sales','Nov 15 1985 12:00AM', UNION ALL
SELECT'Robert','D','Albrecht','Apr 22 1954 12:00AM','bobby.albrecht@mydomain.com','VP Admin/Controller','Feb 1 1999 12:00AM', UNION ALL
SELECT'Heidi','T','Allen','Jun 22 1957 12:00AM','heidi.allen@mydomain.com','Regional Vice President','Apr 24 1990 12:00AM', UNION ALL
--Insert data into AD_Users
SELECT'Julie Abrahamson','Julie','Julie.Abrahamson@mydomain.com','0xFEE91B61E4FCAF4AB480A931A05DA340','0x010500000000000515000000576704770B0A460B5C35015313050000','Abrahamson','2001-01-23 22:48:55.000', UNION ALL
SELECT,'Philip Albers','Philip','Philip.Albers@mydomain.com','0x21FD1D979861A644B33EFCEA0E5463C1','0x010500000000000515000000576704770B0A460B5C350153EB050000','Albers','2001-01-23 22:48:56.000', UNION ALL
SELECT,'Bobby Albrecht','Bobby','Bobby.Albrecht@mydomain.com','0x558334E449FC2C4CA92437A278C119DE','0x010500000000000515000000576704770B0A460B5C35015323190000','Albrecht','2007-03-05 17:37:12.997', UNION ALL
SELECT,'Heidi Allen','Heidi','Heidi.Allen@mydomain.com','0x1FA2E3C9607A394A9558E195407F2DBB','0x010500000000000515000000576704770B0A460B5C350153CA070000','Allen','2003-07-17 16:34:35.000', UNION ALL

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply

john.arnott SSChampion Points: 11882 More actions · Answer 1

Comparison of data can be a bit tricky if you don't have any absolute way to tie the rows of the two tables to each other. Because of this, it sounds as though you want to produce a list of rows in each table that do NOT have a match in the other table. Correct? I've taken a guess at what you need here. Please do post desired output if this doesn't work for you.

First, thank you for including sample data. In the future, please make sure scripts like that actually work -- failed code will turn off a lot of folks who may otherwise really want to help. I've cleaned it up a bit by coding the necessary explicit CONVERTs for the varbinary columns, removing the dates from the HR data as there's no column defined for them, altering the objectSID data to make them unique and removing extraeous columns and "UNION ALL" phrases.

My suggested code finds first or last name fields that differ between the two tables where the email address matches, and also finds mismatched email addresses where both the first and last names do match. It uses an outer join to examine all rows in one table and then report null for the selected columns in the other table where there's no match. I've done this twice, UNIONed together, with the RIGHT OUTER JOIN showing AD rows with no match in HR (e.g. "Trinity"), and the LEFT OUTER JOIN finding HR rows that have no match in AD (e.g. "John").

One more note... The WHERE clause on each SELECT limits output to just those rows that partially match (name or email), but have a discrepancy on the other data. If you want to list all rows present in either table regardless of matching, just remove the WHERE clauses from both sides of the UNION.

--drop table ##ActiveDirectory_Users Drop TABLE ##HR_Users

CREATE TABLE ##ActiveDirectory_Users

(displayName nvarchar(255),

givenName nvarchar(255),

mail nvarchar(255),

ObjectGUID varbinary(255) NOT NULL,

objectSid varbinary(255) NOT NULL,

sn nvarchar(255),

whenCreated datetime);

ALTER TABLE ##ActiveDirectory_Users ADD CONSTRAINT pk_objectSid PRIMARY KEY (objectSid);

--Create HR_Users Table--

CREATE TABLE ##HR_Users (

[p_fname] [nvarchar](255) NULL,

[p_mi] [nvarchar](255) NULL,

[p_lname] [nvarchar](255) NULL,

[p_birth] [nvarchar](255) NULL,

[p_empemail] [nvarchar](255) NULL,

[p_jobtitle] [nvarchar](255) NULL,

) ON [PRIMARY];

--Insert data into AD_Users

Insert into ##ActiveDirectory_Users

SELECT 'Julie Abrahamson','Julie','Julie.Abrahamson@mydomain.com',convert(varbinary,'0xFEE91B61E4FCAF4AB480A931A05DA340'),

convert(varbinary,'0x010500000000000525000000576704770B0A460B5C35777313050000'),

'Abrahamson','2001-01-23 22:48:55.000' UNION ALL

SELECT 'Philip Albers','Philip','Philip.Albers@mydomain.com',convert(varbinary,'0x21FD1D979861A644B33EFCEA0E5463C1'),convert(varbinary,'0x010500000000000535000000576755570B0A460B5C350153EB350000'),'Albers','2001-01-23 22:48:56.000' UNION ALL

SELECT 'Bobby Albrecht','Bobby','Bobby.Albrecht@mydomain.com',convert(varbinary,'0x558334E449FC2C4CA92437A278C119DE'),convert(varbinary,'0x010500000000000545888000576704770B0A460B5C35017323190000'),'Albrecht','2007-03-05 17:37:12.997' UNION ALL

SELECT 'Heidi Allen','Heidi','Heidi.Allen@mydomain.com',convert(varbinary,'0x1FA2E3C9607A394A9558E195407F2DBB'),convert(varbinary,'0x010500000000000555000000576704770B0A45555C350163CA070000'),'Allen','2003-07-17 16:34:35.000' UNION ALL

SELECT 'Trinty Roberts','Trinity','Trinity.Roberts@mydomain.com',convert(varbinary,'0x1FA2E3C9345A394A9558E195407F2DBB'),convert(varbinary,'0x010500000000000555000000765704770B0A45555C350163CA070000'),'Roberts','2002-09-17 16:34:35.000'

--Insert data into HR_Users

Insert into ##HR_Users

SELECT'Julie','K','Abrahamson','Aug 13 1959 12:00AM','julie.abrahamson@mydomain.com','Asst VP Compliance' UNION ALL

SELECT'Philip','A','Albers','May 20 1960 12:00AM','phil.albers@mydomain.com','Assistant VP Sales' UNION ALL

SELECT'Robert','D','Albrecht','Apr 22 1954 12:00AM','bobby.albrecht@mydomain.com','VP Admin/Controller' UNION ALL

Select 'John', '', 'Arnott', 'Jan 12 2009','john.arnott@mydomain.com','CEO' Union all

SELECT'Heidi','T','Allen','Jun 22 1957 12:00AM','heidi.allen@mydomain.com','Regional Vice President'

select * from ##HR_Users

select * from ##ActiveDirectory_Users

Select case when ad.sn = hr.p_lname then '' else '***' end as SurnameDiff

,ad.sn

,hr.p_lname

,case when ad.givenName = hr.p_fname then '' else '***' end as FirstNameDiff

,ad.givenName

,hr.p_fname

,case when ad.mail = hr.p_empemail then '' else '***' end as EMailDiff

,ad.mail

,hr.p_empemail

from ##HR_Users hr

--left

right

outer join ##ActiveDirectory_Users ad

on (ad.sn=hr.p_lname and ad.givenName = hr.p_fname)

or ad.mail = hr.p_empemail

-- WHERE Clause will limit output to just those rows with an identified difference

Where case when ad.sn = hr.p_lname then '' else '***' end = '***'

or case when ad.givenName = hr.p_fname then '' else '***' end = '***'

or case when ad.mail = hr.p_empemail then '' else '***' end = '***'

UNION