CODEWITHSUNDEEP
sql-serverchecksum
developer9969
developer9969

Reputation: 5236

Find all rows that have changed and write old-Newvalue to different table in SQL Server 2012

Need to write to a table all the rows where values have changed between 2 datacuts. This must be done in sql and not using any third party tools.

I can find the difference between 2 datacuts easily by using "Except". I have not tried chksum but added a column just in case.

What I am struggling with and need your help is How do pull all the data out from my findings into my #Changes table?

WANTED RESULT

EmployeeId ColumnName OldValue NewValue 3 MaritalStatus Single Married 3 Surname Malone Evans 10 MaritalStatus Single Married

SETUP TEST DATA Dummy data set up (2 Employees with Id(3,10) have changes) if you notice employee id(3) has 2 columns changes.

IF OBJECT_ID('tempdb..#Employee') IS NOT NULL DROP TABLE #Employee
GO
IF OBJECT_ID('tempdb..#Changes') IS NOT NULL DROP TABLE #Changes
GO

CREATE TABLE #Employee
(
    [Id] [int] NOT NULL,  
    EmployeeNo INT NOT NULL, 
    [DataCut] [int] NULL,
    [Name] [varchar](50) NULL,
    [Surname] [varchar](50) NULL,
    [Gender] [varchar](10) NULL,
    [MaritalStatus] [varchar](10) NULL,
    [Chksum] [int] NULL,

    CONSTRAINT [PK_#Employee] 
        PRIMARY KEY CLUSTERED ([Id] ASC)
) ON [PRIMARY]

CREATE TABLE #Changes
(
    [EmployeeNo] [int] ,
    [ColumnName] [varchar](50) NULL,
    [OldValue] [varchar](50) NULL,
    [NewValue] [varchar](50) NULL
)

INSERT INTO #Employee([Id], EmployeeNo,[DataCut], [Name], [Surname], [Gender], [MaritalStatus],[Chksum])
SELECT 1, 1,1, N'Jo', N'Bloggs', N'Male', N'Single', NULL UNION ALL
SELECT 2, 2,1, N'Mark', N'Smith', N'Male', N'Single', NULL UNION ALL
SELECT 3, 3,1, N'Jenny', N'Malone', N'Female', N'Single', NULL UNION ALL
SELECT 4, 4,1, N'Mario', N'Rossi', N'Male', N'Single', NULL UNION ALL
SELECT 5, 5,1, N'Richard', N'Jones', N'Male', N'Single', NULL UNION ALL
SELECT 6, 1,2, N'Jo', N'Bloggs', N'Male', N'Single', NULL UNION ALL
SELECT 7, 2,2, N'Mark', N'Smith', N'Male', N'Single', NULL UNION ALL
SELECT 8, 3,2, N'Jenny', N'Evans', N'Female', N'Married', NULL UNION ALL
SELECT 9, 4,2, N'Mario', N'Rossi', N'Male', N'Single', NULL UNION ALL
SELECT 10,5,2, N'Richard', N'Jones', N'Male', N'Married', NULL

--Find all the Rows that have changed between 2 datacuts using EXCEPT
SELECT EmployeeNo,Name, Surname, Gender, MaritalStatus 
FROM #Employee 
WHERE DataCut=1

EXCEPT
SELECT EmployeeNo,Name, Surname, Gender, MaritalStatus 
FROM #Employee 
WHERE DataCut=2

UNION

--do the opposite so that we get all the rows.
SELECT EmployeeNo,Name, Surname, Gender, MaritalStatus 
FROM #Employee 
WHERE DataCut=2

EXCEPT
SELECT EmployeeNo,Name, Surname, Gender, MaritalStatus 
FROM #Employee 
WHERE DataCut=1    


--HOW DO I FILL MY #CHANGES TABLES TO MATCH MY WANTED RESULT?

 DROP TABLE #Changes
 DROP TABLE #Employee

Upvotes: 1

Views: 94

Answers (1)

Giorgos Betsos
Giorgos Betsos

Reputation: 72175

You can use UNPIVOT:

;WITH UnpivotedTable AS (
SELECT Id, EmployeeNo, DataCut, Val, Col           
FROM
   (SELECT Id, EmployeeNo, DataCut, CAST(Name AS VARCHAR(50)) AS Name, 
           CAST(Surname AS VARCHAR(50)) AS Surname,
           CAST(Gender AS VARCHAR(50)) AS Gender, 
           CAST(MaritalStatus AS VARCHAR(50)) AS MaritalStatus
    FROM #Employee) AS src
UNPIVOT 
   (Val FOR Col IN 
      (Name, Surname, Gender, MaritalStatus)) AS unpvt
)
SELECT t1.Id As EmployeeId,
       t1.Col AS ColumnName,
       t1.Val AS OldValue, 
       t2.Val AS NewValue
FROM UnpivotedTable AS t1
INNER JOIN UnpivotedTable AS t2 
   ON t1.EmployeeNo = t2.EmployeeNo AND t1.Col = t2.Col AND 
      t1.DataCut = 1 AND t2.DataCut = 2
WHERE t1.Val <> t2.Val

Demo here

Explanation:

Here's an excerpt of the data returned by the CTE (for EmployeeNo = 1):

Id  EmployeeNo  DataCut Val      Col
---------------------------------------------
1   1           1       Jo       Name
1   1           1       Bloggs   Surname
1   1           1       Male     Gender
1   1           1       Single   MaritalStatus
6   1           2       Jo       Name
6   1           2       Bloggs   Surname
6   1           2       Male     Gender
6   1           2       Single   MaritalStatus

Using the above table expression we can easily get the expected result performing an INNER JOIN operation: we just have to compared 'old' (DataCut = 1) vs new (DataCut = 2) values for the same EmployeeNo and Col.

Upvotes: 1

Related Questions