Compare two tables and find differences in column values

Question

I want to compare two tables (identical columns) and find out that what column value changed.

Here is an example with sample data.

employee_original table has 6 columns.

CREATE TABLE [dbo].[employee_original](
    [emp_id] [int] IDENTITY(1,1) NOT NULL,
    [first_name] [varchar](100) NOT NULL,
    [last_name] [varchar](100) NOT NULL,
    [salary] int NOT NULL,
    [city] [varchar](20) NOT NULL,
    [department] [varchar](20) NOT NULL,
PRIMARY KEY CLUSTERED 
(
    [emp_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO

INSERT INTO employee_original VALUES ( 'Julia', 'Schultz', 100, 'New York', 'Tech');
INSERT INTO employee_original VALUES ( 'Vincent', 'Trantow', 200, 'Moscow', 'HR');
INSERT INTO employee_original VALUES ( 'Whitney ', 'Pouros', 500, 'Miami', 'Accounting');
INSERT INTO employee_original VALUES ( 'Chandler', 'Osinski', 10, 'Singapore', 'Purchasing');
INSERT INTO employee_original VALUES ( 'Sydnie', 'Green', 700, 'Ireland', 'Operations');
INSERT INTO employee_original VALUES ( 'Josefa', 'Anderson', 800, 'Berlin', 'Purchase');
INSERT INTO employee_original VALUES ( 'Brayan', 'Bergstrom', 900, 'New York', 'Operations');
INSERT INTO employee_original VALUES ( 'Shyanne', 'Kris', 900, 'New York', 'Sales');

employee_modified has same employee but some of the attributes have changed for few employees.

CREATE TABLE [dbo].[employee_modified](
    [emp_id] [int] IDENTITY(1,1) NOT NULL,
    [first_name] [varchar](100) NOT NULL,
    [last_name] [varchar](100) NOT NULL,
    [salary] int NOT NULL,
    [city] [varchar](20) NOT NULL,
    [department] [varchar](20) NOT NULL,
PRIMARY KEY CLUSTERED 
(
    [emp_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO

INSERT INTO employee_modified VALUES ( 'Julia', 'Schultz', 100, 'New York', 'Tech');
INSERT INTO employee_modified VALUES ( 'Vincent', 'Wyman', 500, 'Moscow', 'HR');
INSERT INTO employee_modified VALUES ( 'Whitney ', 'Pouros', 500, 'Miami', 'Sales');
INSERT INTO employee_modified VALUES ( 'Chandler', 'Osinski', 10, 'Singapore', 'Purchasing');
INSERT INTO employee_modified VALUES ( 'Sydnie', ' Cartwright', 900, 'Ireland', 'Operations');
INSERT INTO employee_modified VALUES ( 'Joseph', 'Anderson', 800, 'Berlin', 'Purchase');
INSERT INTO employee_modified VALUES ( 'Bryan', 'Bergstrom', 900, 'Naples', 'Operations');
INSERT INTO employee_modified VALUES ( 'Shyanne', 'Jakubowski', 900, 'New York', 'Accounting');

I am looking for a result that can tell me what field changed for which employee. e.g. emp_id =2 has last name and salary change. So output should look like:

emp_id  attribute   orignial_value  new_value
2       last_name   Trantow         Wyman
2       salary      200             500

This is what I have tried so far:

(1) Join tables and find what changed :

DROP TABLE IF EXISTS #temp;

SELECT      distinct 
            o.emp_id,
            o.first_name [original_first_name], m.first_name [modified_first_name],
            o.last_name [original_last_name], m.last_name [modified_last_name],
            o.salary [original_salary], m.salary [modified_salary],
            o.city [original_city], m.city [modified_city],
            o.department [original_department], m.department [modified_department]
            into #temp from  
            [dbo].[employee_original] o inner join [dbo].[employee_modified] m on o.emp_id = m.emp_id

select * from #temp

Gives me

(2) Self join with #temp and find out what attribute has changed.

-- All Last Name Changes. 
select distinct t1.emp_id, t1.original_last_name, t2.modified_last_name
        from #temp t1
            inner join #temp t2 on t1.emp_id = t2.emp_id
        where t1.original_last_name <> t2.modified_last_name

-- All Department changes
select distinct t1.emp_id, t1.original_department, t2.modified_department
        from #temp t1
            inner join #temp t2 on t1.emp_id = t2.emp_id
        where t1.original_department <> t2.modified_department

Any pointers on how I can get to my desired result.

user_14868997 · Answer

You can use the following code to unpivot all possible changes

SELECT
  o.emp_id,
  v.column_name,
  v.old_value,
  v.new_value
FROM employee_original o
JOIN employee_modified m ON o.emp_id = m.emp_id
CROSS APPLY (
    SELECT 'first_name', CAST(o.first_name AS nvarchar(max)), CAST(m.first_name AS nvarchar(max))
      WHERE o.first_name <> m.first_name
    UNION ALL
    SELECT 'last_name', o.last_name, m.last_name
      WHERE o.last_name <> m.last_name
    UNION ALL
    SELECT 'salary', o.salary, m.salary
      WHERE o.salary <> m.salary
    UNION ALL
    SELECT 'city', o.city, m.city
      WHERE o.city <> m.city
    UNION ALL
    SELECT 'department', o.department, m.department
      WHERE o.department <> m.department
) v(column_name, old_value, new_value);

user_1570000 · Answer

Here is an option that will dynamically unpivot your data without actually using dynamic SQL.

Example

Select emp_id
      ,[key]
      ,Org_Value = max( case when Src=1 then Value end)
      ,New_Value = max( case when Src=2 then Value end)
 From (
        Select Src=1
              ,emp_id 
              ,B.*
         From [employee_original] A
         Cross Apply ( Select [Key]
                             ,Value
                       From OpenJson( (Select A.* For JSON Path,Without_Array_Wrapper,INCLUDE_NULL_VALUES  ) ) 
                     )  B
        Union All
        Select Src=2
              ,emp_id 
              ,B.*
         From [employee_modified] A
         Cross Apply ( Select [Key]
                             ,Value
                       From OpenJson( (Select A.* For JSON Path,Without_Array_Wrapper,INCLUDE_NULL_VALUES  ) ) 
                     )  B
      ) A
 Group By emp_id,[key]
 Having max( case when Src=1 then Value end)
     <> max( case when Src=2 then Value end)
 Order By emp_id,[key]

Results

Compare two tables and find differences in column values

2 Answers