CODEWITHSUNDEEP
sqlsql-server-2016temporal-tables
Abdul Azeez
Abdul Azeez

Reputation: 895

Sql 2016 - How to get only modified column in system-versioned temporal table

How to get only modified column in system-versioned temporal table.

I am using SQL 2016 system-versioned temporal table to track the changes. I have the history table populated with multiple rows which include modified and not modified columns for each version of row edit.

Is there any way to get only modified column value from history row data.

Appreciated your response.

Thanks

Abdul

Upvotes: 4

Views: 2539

Answers (2)

Vahid Farahmandian
Vahid Farahmandian

Reputation: 6564

Thanks to @Abdul Azeez description, here is the sample code to achieve this goal:

Suppose that I have a table called MyTable, and I want to capture the changes on Name and LastName columns(both have varchar data type) FOR ALL the time.

;WITH T
AS (
    SELECT ID,
           LAG(Name) OVER (PARTITION BY ID ORDER BY SysStartTime, SysEndTime) AS Prev_Name,
           Name,
           LAG(LastName) OVER (PARTITION BY ID ORDER BY SysStartTime, SysEndTime) AS Prev_LastName,
           LastName,
           Email,
           SysStartTime,
           SysEndTime
    FROM dbo.MyTable FOR SYSTEM_TIME ALL
    WHERE Email = '[email protected]'
)
SELECT T.SysStartTime,
       T.SysEndTime,
       C.ColName,
       C.prev_value,
       C.cur_value
FROM T
    CROSS APPLY
    (
        VALUES
            ('Name', T.Name, T.Prev_Name),
            ('LastName', T.LastName, T.Prev_LastName)
    ) AS C (ColName, cur_value, prev_value)
WHERE EXISTS
(
    SELECT cur_value 
    EXCEPT 
    SELECT C.prev_value
);

Note: If the columns have different data types you might need to cast them to an appropriate data type.

Upvotes: 1

Abdul Azeez
Abdul Azeez

Reputation: 895

I used this approach to solve the problem.

  1. Fetch all history and transaction records and Partitioned with Primary key.

  2. Cross Apply the table to make row structure is Column Name and Column Value and of course other IDs like Primary Key, Partitioned Key, etc

  3. Compare Column value based on Column Name, Primary key and Partition Key

Please let me know if any good approach.

Regards
Abdul

Upvotes: 1

Related Questions