Results 1 to 3 of 3

Thread: Oracle to MSSQL Temporal Table

  1. #1
    darkchanter is offline Junior Member
    Join Date
    May 2018
    Posts
    4
    Rep Power
    0

    Oracle to MSSQL Temporal Table

    I am trying to replicate from Oracle into a temporal table on SQL-Server. The target would then provide a history of the changes made in the source table, especially updates and some deletes.


    A temporal table requires some technical attributes. These do not exist in the source table. Attunity seems to fill-in a NULL value/placeholder for every attribute that isn't provided by the source mapping.

    Code:
    create table customerStage (
    id int constraint PK_customerStage primary key,
    startDT datetime2 generated always as row start not null,
    endDT datetime2 generated always as row end not null,
    period for system_time (startDT, endDT),
    someData varchar(50)
    )
    with
    (
    system_versioning = on (history_table = dbo.customerStageHistory)
    )
    To use this kind of tables, we need a way to have attunity ignoring these attributes (startDT, endDT, period) in the internal commands (bcp).

    Otherwise the task fails reporting that we cannot insert a NULL value into those fields.


    As a work-around, I replicate data from oracle into a "normal" table on SQL-Server. This table fires triggers to promote the operations to the actual temporal table.

  2. #2
    stevenguyen is offline Senior Member
    Join Date
    May 2014
    Posts
    301
    Rep Power
    6
    it sounds like you are looking for Store changes , this is already a part of Replicate.

    See user guide on Working with Change Tables

  3. #3
    darkchanter is offline Junior Member
    Join Date
    May 2018
    Posts
    4
    Rep Power
    0
    Well, yes - that was my second way to try. For now this Looks very convenient.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •