Copy temporal table 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.

Thank you

To complete the previous answer, it is possible to replicate a temporal table within the same database but with a different table name. You don't necessarily need to create a new database for this purpose.

You can follow the same steps as mentioned earlier, except that you would create the new temporal table with a different name and in the same database. For example:

1. Make a full backup of the database that contains the temporal table using the SQL Server Management Studio (SSMS) or Transact-SQL command BACKUP DATABASE.

2. Restore the backup on the same server or the same database with a different name using the SSMS or Transact-SQL command RESTORE DATABASE.

3. Once the database is restored, create a new temporal table with a different name but with the same schema as the source temporal table. To do this, you can run the following Transact-SQL script:

> CREATE TABLE [dbo].[NewTemporalTable] (
> [Id] INT NOT NULL,
> [Name] VARCHAR(50),
> [StartDate] DATETIME2 GENERATED ALWAYS AS ROW START HIDDEN NOT NULL,
> [EndDate] DATETIME2 GENERATED ALWAYS AS ROW END HIDDEN NOT NULL,
> PERIOD FOR SYSTEM_TIME ([StartDate], [EndDate]),
> CONSTRAINT [PK_NewTemporalTable_Id] PRIMARY KEY CLUSTERED ([Id])
> ) WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = [dbo].[NewTemporalTableHistory]));

This will create a new temporal table named "NewTemporalTable" with the same schema as the source temporal table, along with a new history table named "NewTemporalTableHistory".

4. Disable system versioning on the new temporal table by running the following command:

> ALTER TABLE [dbo].[NewTemporalTable] SET (SYSTEM_VERSIONING = OFF);

5. Insert the data from the source temporal table into the new temporal table, excluding the system-versioned columns. You can achieve this by specifying the column names explicitly as follows:

   INSERT INTO [dbo].[NewTemporalTable] ([Id], [Name])
   SELECT [Id], [Name] FROM [dbo].[SourceTemporalTable];

6. Re-enable system versioning on the new temporal table by running the following command:

> ALTER TABLE [dbo].[NewTemporalTable] SET (SYSTEM_VERSIONING = ON
> (HISTORY_TABLE = [dbo].[NewTemporalTableHistory]));

You have now replicated a temporal table within the same database but with a different name, along with its entire history.

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

It is possible to replicate temporal tables in SQL Server with its entire history. Here are the steps:

1. Create a new database as destination.

2. In the new database, create a empty table with the same schema as the original temporal table.

3. Disable system versioning on the new table using the ALTER TABLE statement:

   ALTER TABLE [NewTable] SET (SYSTEM_VERSIONING = OFF);

4. Insert data from the original temporal table into the new table using the SELECT INTO statement:

   INSERT INTO [NewTable]
   SELECT *
   FROM [OriginalTemporalTable]
   

5. Enable system versioning on the new table:

   ALTER TABLE [NewTable] SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = [NewTableHistory]));