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

Oracle Data Pump Can't Import LONG Columns

Automation, PowerShell and Word Templates - let the technician do tech

Datascape Episode 55: The State of Database Security in 2022

Ready to unlock value from your data?