none
Temporal Table Migration from VM to Azure SQL RRS feed

  • Question

  • I recently migrated my SQL 2019 database from a VM into Azure SQL.
    I used the MS Data Migration tool, but unfortunately, it wouldn't migrate data from Temporal Tables.

    So. I just used the tool to create the table schemas and then used SSIS to move the data.  

    Since my existing history table had data in it, I wanted to keep the SysStartDate and SysEndDate fields.  In order to do this, I had to disable SYSTEM_VERSIONING in my Azure SQL database as well as DROP the PERIOD on the table.

    The data migration was a success so I re-created my PERIOD on the table but when I tried to enable SYSTEM_VERSIONING with a specified history table, I get the following error:

    >Msg 13573, Level 16, State 0, Line 34<br>
    Setting SYSTEM_VERSIONING to ON failed because history table 'xxxxxHistory'<br> contains overlapping records.

    I find this odd because the existing tables were originally joined as a temporal table so I don't understand why there would be a conflict now.

        ALTER TABLE xxx.xxx 
        ADD PERIOD FOR SYSTEM_TIME(SysStartTime, SysEndTime)

        ALTER TABLE xxx.xxx 
        SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE=xxx.xxxHistory))

    I expect to get a successful temporal table. Instead, I get the following error: 

    >Msg 13573, Level 16, State 0, Line 34<br>
    Setting SYSTEM_VERSIONING to ON failed because history table 'xxxxxHistory'<br> contains overlapping records.

    I ran the following query to identify the overlaps but I don't get any:

        SELECT 
        xxxxKeyNumeric
        ,SysStartTime
        ,SysEndTime
        FROM 
        xxxx.xxxxhistory o
        WHERE EXISTS
        (
            SELECT 
       
        FROM 
        xxxx.xxxxhistory o2
            WHERE 
        o2.xxxxKeyNumeric = o.xxxxKeyNumeric
        AND o2.SysStartTime <= o.SysEndTime
        AND o.SysStartTime <= o2.SysEndTime
        AND o2.xxxxPK != o.xxxxPK
        )
        ORDER BY 
        o.xxxxKeyNumeric, 
        o.SysStartTime
    Wednesday, July 17, 2019 5:09 PM

All replies

  • Hi johnny51714,

    >> Setting SYSTEM_VERSIONING to ON failed because history table 'xxxxxHistory'<br> contains overlapping records.

    The error message means that there are multiple records for the same record with overlapping start and end dates. The end date for the last row in the history table should match the start date for the active record in the parent table.

    Please try to use below T-SQL check again.

    SELECT * 
    FROM ResourceTDHistory 
    WHERE EffectiveStartDate > EffectiveEndDate

    Hope this could help you.

    Best regards,
    Cathy Ji 

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Thursday, July 18, 2019 8:20 AM