Oracle Technical Track

Using UNPIVOT with CDC Functions to get Updated Columns List

1 min read

Nov 29, 2010 12:00:00 AM

Microsoft introduced Change Data Capture (CDC) technology in SQL Server 2008. This technology captures DML (insert/update/delete) changes to a table. After CDC is enabled for a database and a given table, one can use cdc.fn_cdc_get_all_changes_ function to query changes made to the table. cdc.fn_cdc_get_all_changes_ function returns all columns from the table, even when only one column was updated. It also returns __$update_mask column which is a bit mask that shows which columns were updated, but still - the whole row is returned. When calling this function with "all_update_old" option it returns two records for each update: one with "before update" values and one with "after update" values. The challenge was to produce a "log" table with columns like this:

commit_time

column_name

old_value

new_value

UNPIVOT operator helps us to produce desired result. Here's an example query: [code lang="SQL" wraplines="false" highlight="9,17"] SELECT sys.fn_cdc_map_lsn_to_time(up_b.__$start_lsn) as commit_time, up_b.column_name, up_b.old_value, up_a.new_value FROM ( SELECT __$start_lsn, column_name, old_value FROM (SELECT __$start_lsn, CASE WHEN (sys.fn_cdc_is_bit_set (sys.fn_cdc_get_column_ordinal ('dbo_','CDC_column_1'),__$update_mask) = 1) THEN CAST([CDC_column_1] as sql_variant) ELSE NULL END AS [CDC_column_1], CASE WHEN (sys.fn_cdc_is_bit_set (sys.fn_cdc_get_column_ordinal ('dbo_','CDC_column_2'),__$update_mask) = 1) THEN CAST ([CDC_column_2] as sql_variant) ELSE NULL END AS [CDC_column_2], .... FROM cdc.fn_cdc_get_all_changes_dbo_(@from_lsn, @to_lsn, N'all update old') WHERE __$operation = 3) as t1 UNPIVOT (old_value FOR column_name IN ([CDC_column_1], [CDC_column_2], ...) ) as unp) as up_b -- before update INNER JOIN (SELECT __$start_lsn, column_name, new_value FROM (SELECT __$start_lsn, CASE WHEN (sys.fn_cdc_is_bit_set (sys.fn_cdc_get_column_ordinal ('dbo_','CDC_column_1'),__$update_mask) = 1) THEN CAST([CDC_column_1] as sql_variant) ELSE NULL END AS [CDC_column_1], CASE WHEN (sys.fn_cdc_is_bit_set (sys.fn_cdc_get_column_ordinal ('dbo_','CDC_column_2'),__$update_mask) = 1) THEN CAST ([CDC_column_2] as sql_variant) ELSE NULL END AS [CDC_column_2], .... FROM cdc.fn_cdc_get_all_changes_dbo_(@from_lsn, @to_lsn, N'all') -- 'all update old' is not necessary here WHERE __$operation = 4) as t2 UNPIVOT (new_value FOR column_name IN ([CDC_column_1], [CDC_column_2], ...) ) as unp ) as up_a -- after update ON up_b.__$start_lsn = up_a.__$start_lsn AND up_b.column_name = up_a.column_name [/code]

On this page

Ready to unlock value from your data?

With Pythian, you can accomplish your data transformation goals and more.

Speak with Pythian consultants now →

What We Do

Using UNPIVOT with CDC Functions to get Updated Columns List

Share this

Share this

More resources

MariaDB Temporal Tables: Uncut & Uncensored (Part II)

After logon trigger in user schema and FRA full situation hang the database

SQL server: Create missing indexes with unmessing names

Ready to unlock value from your data?