DSXchange

Hi All,

How do I perform change data detection between the tables as we are loading the files data into the tables we want to detect the changes of new and yesterdays file . Let me explain more detail we have two tables coming which has yesterdays data and todays data How do i perform the changes . As I have asked this question earlier but then i thought it was files but we do not have access to files so we are loading the files data into the staging tables and we should perform change data detection from then on.

Try to help me out with valuable suggestions

Thanks

Table... file... doesn't really make a difference in what you need to do. So whatever advice you got in the other thread should still hold.

I can perform the change data detection by joining the tables as suggested in the earlier thread ,but I don't have any key columns in both the tables to perform the join , how could i do it

Any valuable answers

Thanks

If I understood this correctly then i think you can compare all the columns in the file with a target table Hashed file(which can be in line or populated when the load ran last time) and determine whether it is already in the target table or not.
If the number of columns are many then you can use functions like CRC etc.

Thanks Deepak,

But I am not comparing the files , but joining the tables to perform change data capture , because in the earlier thread kcblend was suggesting to join the tables and do a left join to capture the new records that are coming in ,but here i don't have any key columns in the tables can i join those two tables without any key.

I appreciate your help

Do a join on all the columns. Or someone has to identify keys for you to capture change.

I have joined the two tables by using left outer join to get the new ones and my job design is

ODBC STAGE -------> TRANSFORMER----------> ODBC STAGE


In this i am joining table A(today's data) left outer join table b (yesterday's data) and putting these changes into table c but since when i am trying to run this i am getting al the 22 rows which has the two new changed records and whole previous what do i do i just want only two rows which are changes should be in the table C And for table C i have given the update action as "CLEAR THE TABLE THEN INSERT THE NEW ROWS"

Your valuable suggestions would be appreicated.

Thanks



[/img]

Again , did you try using a hashed file as a tgt lookup and filtering out the rows which are found in the hashed file? If you do not have key columsn u can mark all of them as keys or can use something like a CRC logic.

While doing a join, select TableB's key a well. All new records will have null key from TableB. Filter that in the transformer by putting a contraint. Or you can also filter in the sql select.

Start over.
Document (not in pseudo code, but in English or any other spoken language) what you have to do.
This will make the design requirements easier for you, and for us, to understand.