merge a partition temporal table

Database
Enthusiast

merge a partition temporal table

I have a temporal table which is  PARTITION BY End(validity COLUMN) , So I can retreive current open rows in a fast way,

 

But when I try to merge the temporal table from a source table
it show an error
TARGET TABLE PRIMARY INDEX AND PARTITION COLUMN(s) AND expression must match INSERT specification PRIMARY INDEX AND PARTITION COLUMN(s)

 

When I place a condition on end(validity) to show to me another error

 

VALIDTIME COLUMN should NOT be referenced WHEN the qualifier IS SEQUENCED VALIDTIME WITH a PERIOD OF applicability.

 

 

My Code is 

 

drop table Src_Table;
drop table tar_Table;

 

CREATE MULTISET TABLE Src_Table(
Cust_ID INTEGER,
Cust_Name varchar (10) NOT NULL
)
PRIMARY INDEX(Cust_ID);

insert into Src_Table(1,'Ahmed1');
insert into Src_Table(2,'Ahmed2');
insert into Src_Table(3,'Ahmed3');
insert into Src_Table(4,'Ahmed4');


CREATE MULTISET TABLE tar_Table(
Cust_ID INTEGER,
Cust_Name varchar (10) NOT NULL,
Validity PERIOD(date) NULL AS VALIDTIME
)

PRIMARY INDEX(Cust_ID)
partition by (
case_n(end(validity)=date'9999-12-31',no case , unknown)
)
;
SEQUENCED VALIDTIME PERIOD (DATE '2017-02-09',UNTIL_CHANGED)
MERGE INTO tar_Table
USING (SELECT *
              FROM Src_Table
             ) AS source
ON tar_Table.Cust_ID = source.Cust_ID
--and end(validity)=date'9999-12-31'  this line cause an error if I leave it or remove it
WHEN MATCHED THEN
UPDATE SET Cust_Name = source.Cust_Name
WHEN NOT MATCHED THEN
INSERT (source.Cust_ID, source.Cust_Name,PERIOD (DATE '2017-02-09',UNTIL_CHANGED));
;

2 REPLIES
Enthusiast

Re: merge a partition temporal table

Any help ?

Teradata Employee

Re: merge a partition temporal table

You can't do a MERGE like that. You can do sequenced UPDATE:

 

SEQUENCED VALIDTIME PERIOD(DATE'2017-02-09',UNTIL_CHANGED)
UPDATE T FROM TAR_TABLE T, SRC_TABLE S
SET CUST_NAME = S.CUST_NAME
WHERE T.CUST_ID = S.CUST_ID;

But inserting history rows is potentially more involved. You may need to do NONSEQUENCED processing to determine the correct ending bound to use when inserting the new row, and to update the validity period of existing rows.