scd type 2 in mload

Tools

scd type 2 in mload

can any one give the example .how to implement scd type2 in mload
6 REPLIES
Enthusiast

Re: scd type 2 in mload

using upsert comand we can update the previous records. if we want to insert new records we can insert using same command

Re: scd type 2 in mload

hi rajan,

We can implement the scd type 2with mload. I wl show the sample script

.dml label lbinsert
insert into tabname values(:id,:name,:loc,:std_dt,'0001-01-01');

.dml label lbupdate
do insert for missing update rows;
update tabname set end=:std_dt where id=:id;
insert into tabname values(:id,:name,:loc,:std_dt,'0001-01-01');

.import infile c:\mload\file.txt format vartext ','

layout XXXX

apply lbupdate apply lbinsert;
end mload;
logoff;

This is the how we can implement scd type 2 with multi load as per my knowledge.

u can tryout this i think its work..

bye.

thnx

Re: scd type 2 in mload

using upsert comand we can update the previous records
gkk
Enthusiast

Re: scd type 2 in mload

hai bab,

The Above code doesn't work in implementing SCD Type 2 via MLOAD . Because by giving both "apply lbupdate apply lbinsert"

for a new record it will effect both dml labels .
for a record already exist the old record and new record end_date value is set with '0001-01-01'

Please some one give better solution , even i am in search for it.

bye.
Enthusiast

Re: scd type 2 in mload

please let us know script once got the solution

Re: scd type 2 in mload

Hi All,

I am new to Teradata. I have never tried the following MLOAD code in production environment...But this is working in my PC environment.

.LOGTABLE Practise.emp1_lg;

.RUN FILE Logon1.txt;

.BEGIN  MLOAD TABLES Practise.emp1;

.layout Imp_Layout;

.FIELD Employee_Number * VARchar(13);

.FIELD Dept_Number * VARchar(13);

.FIELD Emp_Mgr_Number * VARchar(13);

.FIELD Job_Code * VARchar(13);

.FIELD Last_Name * VARchar(20);

.FIELD First_Name * VARchar(20);

.FIELD  Salary_Amount * VARchar(20);

.DML LABEL UPD IGNORE MISSING UPDATE ROWS;

UPDATE PRACTISE.EMP1 SET Curr_ind_flg = 0 WHERE Employee_Number = :Employee_Number;

INSERT INTO practise.EMP1 VALUES (:Employee_Number

,:Dept_Number

,:Emp_Mgr_Number

,:Job_Code

,:Last_Name

,:First_Name

,:Salary_Amount

,1);

.IMPORT INFILE source_data.txt

FORMAT VARTEXT ','

LAYOUT Imp_Layout

APPLY UPD;

.END MLOAD;

.LOGOFF;

The above code will update the Curr_ind_flg of existing records as '0' and will insert the new data with the flag as '1'. New records (which didnt exist previously) will also be inserted with flag as '1'. It will not enter any records in the Error table if update is not processed for a new Emp_ID as it should not.

Please let me know if the above code is helpful or not.

Bye!!