Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Vertical Data from Change Data Capture

Answered Vertical Data from Change Data Capture

  • Tuesday, January 29, 2013 9:46 PM
     
      Has Code
    Sign In to Vote
    0

    I'm just starting out with Change Data Capture and haven't found any resources that explain how to capture just the data that has changed into a vertical format. For instance, if I have the following records:


    __$operation Contact_Id Last_Name First_Name
    3 1 Smith John
    4 1 Smithe John

    I want the data to be displayed as:

    Contact_Id Column_Name From_Value To_Value
    1 Last_Name Smith Smithe

    I can certainly use a script like this:

    SELECT	dc.Contact_Id ,
		Column_Name ,
		dc.Last_Name AS From_Value,
		dc2.Last_Name AS To_Value
FROM	cdc.captured_columns AS cc ,
		cdc.dbo_contacts AS dc
		JOIN cdc.dbo_contacts AS dc2 ON dc.__$start_lsn = dc2.__$start_lsn
WHERE	sys.fn_cdc_is_bit_set( Column_ordinal,dc.__$update_mask) = 1
		AND dc.__$operation = 3
		AND dc2.__$operation = 4

    But, that is static and I'd have to have one for each column I'm capturing. Seems like there should be a better, more dynamic way of doing this.

    Anyone have any thoughts?

    Lefka






    • Edited by Lefka356 Tuesday, January 29, 2013 9:47 PM
    • Edited by Lefka356 Tuesday, January 29, 2013 9:51 PM
    • Edited by Lefka356 Tuesday, January 29, 2013 9:54 PM Formatting
    • Edited by Lefka356 Tuesday, January 29, 2013 9:55 PM
    • Edited by Lefka356 Wednesday, January 30, 2013 12:44 AM
    •  
     

All Replies

  • Wednesday, January 30, 2013 1:03 AM
     
     
    Sign In to Vote
    0

    As I understand:

    You have a list of operations. Two or more of these operatiosn have the same forreing key (Contact_ID). In your example data there are only two changes for each foreign key and only one of the possible changes is made (only first name OR last name is changed). Is this an absolute? Or could there be data like

    3     1     Smith     John
    4     1     Smithe   John
    5     1     Schmied Johnas
    6     1     Smith     John

    And what would be your intended output for it?


     
  • Wednesday, January 30, 2013 1:09 AM
     
     
    Sign In to Vote
    0

    3 and 4 are the values for __$operation in the CDC tables. 3 indicates the value for the entire record prior to an update. 4 indicates the value for the entire record after an update. So the table you referenced would never have a 5 and 6 value in the first column. The 1 in the second column is the Primary Key of the source table. So your example is better written:

    3     1     Smith     John
    4     1     Smithe   John
    3     1     Smithe John
    4     1     Schmied     Johnas

    This would indicate one change where the last name changed from Smith to Smithe and a second change where Smithe changed to Schmied and John changed to Johnas. In this example I would expect data to look like this:

    1     Last_Name     Smith     Smithe

    1     Last_Name     Smithe    Schmied

    1     First_Name     John     Johnas

    I hope that is a little more clear. 

    Lefka

     
  • Wednesday, January 30, 2013 2:05 AM
     
     Answered
    Sign In to Vote
    0
    I was able to get this to work with an UNPIVOT statement.

    Lefka

    • Marked As Answer by Lefka356 Wednesday, January 30, 2013 2:05 AM
    •