none
Moving Temporal Tables RRS feed

  • Question

  • I would like to move some temporal tables to a new database on the same server. What would be the best method for this?
    Monday, December 2, 2019 5:20 PM

Answers

All replies

  • 1. Set SYSTEM_VERSIONING off 

    2. Copy both temporal and history tables to the new database 

    3. Set SYSTEM_VERSIONING on in the new database


    A Fan of SSIS, SSRS and SSAS


    Monday, December 2, 2019 5:35 PM
  • Hi ClevelandSteve,

     

    Would you please use ALTER TABLE Yourtable SET ( SYSTEM_VERSIONING = OFF )? Temporal tables would become  regular tables shown in ssms.  And you can use Import-and-Export Wizard to move Temporal Tables.

     

    When you re-enable it , SQL Server will re-create a Temporal Table.

     

    Best regards,

    Dedmon Dai


    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

    Tuesday, December 3, 2019 8:06 AM
  • I have done this but I get the error "Cannot insert an explicit value into a GENERATED ALWAYS column".
    Thursday, December 5, 2019 10:32 PM
  • What did you do when you encounter the error?

    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

    Friday, December 6, 2019 8:58 AM
  • I'm having the same issue. I want to move temporal tables to another database but it seems you can't move the data within temporal tables because of the error specified by ClevelandSteve namely, "Cannot insert an explicit value into a GENERATED ALWAYS column". This needs to be sorted by Microsoft as there could be numerous reasons why data in temporal tables needs to be moved to another database i.e. archiving of old data  

    Cheers, AndySugs

    Thursday, January 16, 2020 5:00 PM
  • Friday, January 17, 2020 5:40 PM
  • That's fine if you just want to copy the table schema to new database location but it does not allow you to move existing data as get the error "Cannot insert an explicit value into a GENERATED ALWAYS column" which is the point Cleveland Steve and I both made. There needs to be a way to allow generated column to be turned off so data can be inserted & then back on

    Cheers, AndySugs

    Thursday, May 7, 2020 11:08 AM
  • Did you follow the steps in the article "SQL Server: How to Move Temporal Table from One Database to Another Database"? You just create two normal tables (one for temporal and the other for history) in the new database. And then you move data to both new tables. Finally you switch them to the temporal table.

    A Fan of SSIS, SSRS and SSAS

    Thursday, May 7, 2020 2:45 PM