Dynamic Merge Statement in TPT

Tools & Utilities
Highlighted
Teradata Employee

Dynamic Merge Statement in TPT

 

Hello,

 

I am trying to make dynamic merge statements using tpt but whenever i run tpt, it ends up with following error 

 

LOAD_OPERATOR: TPT10508: RDBMS error 2556: Duplicate match tag found. (please see following log for further detail)

 

Log File

--------------------------------------------------Tue 01/16/2018 20:09:57.38------------------------------------------------------------ 

Teradata Parallel Transporter Version 15.10.01.02 64-Bit
Job log: C:\Program Files\Teradata\client\15.10\Teradata Parallel Transporter/logs/demo_tpt_tpt_cdc_Demo_RESTORE-1379.out
Job id is demo_tpt_tpt_cdc_Demo_RESTORE-1379, running on WPKZA186003-650
Found CheckPoint file: C:\Program Files\Teradata\client\15.10\Teradata Parallel Transporter/checkpoint\demo_tpt_tpt_cdc_Demo_RESTORELVCP
This is a restart job; it restarts at step MAIN_STEP.
Teradata Parallel Transporter Update Operator Version 15.10.01.02
LOAD_OPERATOR: private log not specified
Teradata Parallel Transporter DataConnector Operator Version 15.10.01.02
FILE_READER[1]: Instance 1 directing private log report to 'dtacop-za186003-19316-1'.
FILE_READER[1]: DataConnector Producer operator Instances: 1
FILE_READER[1]: ECI operator ID: 'FILE_READER-19316'
FILE_READER[1]: Operator instance 1 processing file '\demo_tpt\DEMO_TPT_TPT_CDC_DEMO.csv'.
LOAD_OPERATOR: connecting sessions
LOAD_OPERATOR: preparing target table(s)
LOAD_OPERATOR: entering DML Phase
a
LOAD_OPERATOR: disconnecting sessions
FILE_READER[1]: Total files processed: 0.
LOAD_OPERATOR: Total processor time used = '0.3125 Second(s)'
LOAD_OPERATOR: Start : Tue Jan 16 20:10:00 2018
LOAD_OPERATOR: End : Tue Jan 16 20:10:02 2018
Job step MAIN_STEP terminated (status 12)
Job demo_tpt_tpt_cdc_Demo_RESTORE terminated (status 12)
Job start: Tue Jan 16 20:09:58 2018
Job end: Tue Jan 16 20:10:02 2018

 

 

TPT Script

USING CHARACTER SET UTF8
DEFINE JOB "demo_tpt_tpt_cdc_Demo_1516018197546"
DESCRIPTION 'Data Loading Job'
(
DEFINE SCHEMA tpt_cdc_Demo_SCHEMA
DESCRIPTION 'SCHEMA DETAILS'
(
Product_ID VARCHAR(4)
, Product_Name VARCHAR(40)
, Product_Code VARCHAR(15)
, Product_History VARCHAR(15)
, Created_Date VARCHAR(19)
, Updated_Date VARCHAR(19)
, End_Date VARCHAR(19)
);
DEFINE OPERATOR FILE_READER
DESCRIPTION 'TERADATA PARALLEL TRANSPORTER DATA CONNECTOR OPERATOR'
TYPE DATACONNECTOR PRODUCER
SCHEMA tpt_cdc_Demo_SCHEMA
ATTRIBUTES
(
VARCHAR DIRECTORY_PATH = 'C:\TeradataOffice\td2tdapp\demo_tpt\'
,VARCHAR FileName = 'C:\TeradataOffice\td2tdapp\demo_tpt\DEMO_TPT_TPT_CDC_DEMO.csv'
,VARCHAR IndicatorMode = 'N'
,VARCHAR OpenMode = 'Read'
,VARCHAR Format = @Format
,VARCHAR TextDelimiter = ','
);
DEFINE OPERATOR LOAD_OPERATOR
DESCRIPTION 'TERADATA PARALLEL TRANSPORTER LOAD OPERATOR'
TYPE UPDATE
SCHEMA tpt_cdc_Demo_SCHEMA
ATTRIBUTES
(
VARCHAR TdpId = @TargetTdpId
,VARCHAR UserName = @TargetUserName
,VARCHAR UserPassword = @TargetUserPassword
,VARCHAR TargetTable = 'TPT_Test.tpt_cdc_Demo'
,VARCHAR ErrorTable1 = 'TPT_Test.tpt_cdc_Demo_ET1'
,VARCHAR ErrorTable2 = 'TPT_Test.tpt_cdc_Demo_ET2'
,VARCHAR LogTable = 'TPT_Test.tpt_cdc_Demo_LT'
,INTEGER MaxDecimalDigits = 38
,VARCHAR SpoolMode = 'NoSpoolOnly'
,INTEGER MaxSessions=32
,INTEGER Minsessions=8
,VARCHAR DateForm='AnsiDate'
);
APPLY (' MERGE INTO TPT_TEST.tpt_cdc_Demo
USING VALUES (50,''NewTransaction'',''0024'',''50'',''2018-01-10 10:14:59'',''2018-01-10 10:14:59'',''2018-01-10 10:14:59'')
AS Dept (Product_ID, Product_Name, Product_Code, Product_History,
Created_Date, Updated_Date, End_Date)
ON Dept.Product_ID =tpt_cdc_Demo.Product_ID
WHEN MATCHED THEN UPDATE
SET End_Date = Dept.End_Date
WHEN NOT MATCHED THEN INSERT
VALUES (Dept.Product_ID, Dept.Product_Name, Dept.Product_Code, Dept.Product_History,
Dept.Created_Date, Dept.Updated_Date, Dept.End_Date);')
TO OPERATOR (LOAD_OPERATOR)
SELECT * FROM OPERATOR (FILE_READER[1]););
);

 

 

Any idea?