I have a task to load existing SQL Server table to Teradata temporal table. Existing table is a type 2 table and has many versions of record. I need to load them into teradata temporal table. I am planning to load version 1 1st and then update all other versions one by one. Difficulties i am having is that in existing table every record has start time and end time. I need to update that time in teradata temporal table as validity.
1st I am trying to insert and while insert i am not able to insert end time as less than current time. It report error as "Check constraint violation". Below is sample piece of code for creating table and inserting.
I am yet to test updates as not able to do 1st step.
CREATE multiset TABLE EDW_T.edw_Contracts_History_Test
(
ID INTEGER,
Validity PERIOD(TIMESTAMP(3)) NOT NULL AS VALIDTIME
);
insert into EDW_T.edw_Contracts_History_Test(id,Validity) values(
1,period(cast('1996-01-20 05.00.00.000' as TIMESTAMP(3)), cast('2016-06-23 21.52.20.000' as TIMESTAMP(3))))
--this pass as 2016 is greater than current date
insert into EDW_T.edw_Contracts_History_Test(id,Validity) values(
1,period(cast('1996-01-20 05.00.00.000' as TIMESTAMP(3)), cast('2015-06-23 21.52.20.000' as TIMESTAMP(3))))
--This fails as i m trying to give end time less than current date.
Is there anyway to give end time as less than current date. any way to disable this constraint for time and then enable.
Please help. Thanks!
To insert history rows you should use Sequnce Valid Time Modifier...
Eg :
SEQUENCED VALIDTIME
insert into EDW_T.edw_Contracts_History_Test(id,Validity) values(
1,period(cast('1996-01-20 05.00.00.000' as TIMESTAMP(3)), cast('2015-06-23 21.52.20.000' as TIMESTAMP(3))));