sqlsql-servertemporal-tables

Can I copy a temporal table in SQL Server?

I have a temporal/system versioned table in SQL server and I would like to take a copy/replicate the table along with entire history from the original temporal table.

Is it possible to replicate/copy temporal table in SQL server and if so, what would be the steps?

It's just a one time copy of the current table available.

Solution

  • I don't really see what's the issue here to just make a copy like you would with any other table. Just do it twice, once for main table and once for history table.

    If you have table Department with temporal table DepartmentHistory, you can simply SELECT INTO copies for both.

    SELECT * INTO Department_COPY FROM Department
SELECT * INTO DepartmentHistory_COPY FROM DepartmentHistory

    Here is a dbfiddle sample of this

    Obviously, SELECT INTO will just be a simple backup, if you want new tables to have all the keys and keep being used afterwards, you can CREATE them manually first and then use

    INSERT INTO Department_COPY SELECT * FROM Department
INSERT INTO DepartmentHistory_COPY  SELECT * FROM DepartmentHistory

    If you want to enable your history tracking on copies, you can do it with:

    ALTER TABLE Department_COPY ADD PERIOD
FOR SYSTEM_TIME([ValidFrom], [ValidTo]);

ALTER TABLE Department_COPY SET (
    SYSTEM_VERSIONING = ON (
        HISTORY_TABLE = dbo.DepartmentHistory_COPY,
        DATA_CONSISTENCY_CHECK = ON
    )
);