SQL/Functions to capture type2 data

Database
Enthusiast

SQL/Functions to capture type2 data

Hi,

 

I have a scenario where i have to compare the record from source to the data in the target database, if it already exists. if it does, i have to skip it. if new record comes (values doesnt match) then i have to insert it with date effectivity of future date and update the date of old record.

 

I tried it with row number over rank.Is there any other better approch to do this? Any suggestions are much appreciated and will be of great help to me.

 

Thanks in advance.

2 REPLIES
Teradata Employee

Re: SQL/Functions to capture type2 data

Enthusiast

Re: SQL/Functions to capture type2 data

I did not think of merge. I have used it in oracle, i will try it.

 

I am not aware of PERIOD, i will research more on it. Thank you.

 

I am using buckethash(hasrow) and comparing the values.


GJColeman wrote:

Have you considered using the MERGE DML statement?  (e.g. http://info.teradata.com/htmlpubs/DB_TTU_16_00/index.html#page/SQL_Reference/B035-1146-160K/taq14722...)

Also, for effective dates consider using the PERIOD data type.  http://info.teradata.com/htmlpubs/DB_TTU_16_00/index.html#page/SQL_Reference%2FB035-1143-160K%2Fphj1...



GJColeman wrote:

Have you considered using the MERGE DML statement?  (e.g. http://info.teradata.com/htmlpubs/DB_TTU_16_00/index.html#page/SQL_Reference/B035-1146-160K/taq14722...)

Also, for effective dates consider using the PERIOD data type.  http://info.teradata.com/htmlpubs/DB_TTU_16_00/index.html#page/SQL_Reference%2FB035-1143-160K%2Fphj1...