Implementing SCD type 2 using ANSI Merge in teradata

Database

Implementing SCD type 2 using ANSI Merge in teradata

Hi,

I am trying to implement SCD type 2 using ANSI MERGE. Could anyone please provide a example on how to implement this.

I was searching on the web and came across a article which explains SCD type 2 using MERGE but this is for SQL server DB.

http://www.mssqltips.com/sqlservertip/2883/using-the-sql-server-merge-statement-to-process-type-2-sl...

I would want the same functionality in teradata.

Appreciate your help.

Thanks,

John

4 REPLIES
Junior Contributor

Re: Implementing SCD type 2 using ANSI Merge in teradata

Hi John,

you can't do that using a single TD MERGE, you would need an INSERT plus an UPDATE for WHEN MATCHED, which is not supported.

But this is exactly why TD added Temporal tables :-)

Dieter

Re: Implementing SCD type 2 using ANSI Merge in teradata

Thanks Dieter. This awnsers my question.

Enthusiast

Re: Implementing SCD type 2 using ANSI Merge in teradata

Hi Dieter,

Can you please share a sample code for implementing the SCD2 implementation..which can be refered to.

Regards

Anvesh

Teradata Employee

Re: Implementing SCD type 2 using ANSI Merge in teradata

Dieter that's not technically true using Informatica and BTEQ.  I will explain.  For example from source to stage I load the data.  From Stage to image one, I perform a minus query against the existing core table to identify potential 'U' update records.  Depending on whether or not I am utilizing PDO, I can either nest the left join outside of the dervied table and identify the I, and potential U.  During this step and can set the new end date for the 'I'.  I then insert the results into image 1. 

I then query image 1 can create one additional column that is hexidecimal concatenate can compare that hex value to the core hex value to. Casting nulls as a default value to make sure I'm comparing the right values.  I then set my insert new dates, end date my old column and set new dates for my updates.  I route this using a router,union it and insert into the final work table that mirrors my core table. 

Finally, I call a bteq script with a merge statement and there you have it... Using a merge for Type II SCD.