将历史数据加载到Teradata时态表 [英] Load Historical data to teradata temporal table

问题描述

我有一个任务将现有的SQL Server表加载到Teradata时态表.现有表是2类表，并且具有许多版本的记录.我需要将它们加载到Teradata时态表中.我打算加载版本1 1st，然后一个一个地更新所有其他版本. 我遇到的困难是，在现有表中，每条记录都有开始时间和结束时间.我需要在Teradata时态表中更新该时间作为有效性.

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.

请帮助.谢谢！

推荐答案

要插入历史记录行，应使用序列有效时间修饰符...

To insert history rows you should use Sequnce Valid Time Modifier...

例如:

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))));

这篇关于将历史数据加载到Teradata时态表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！