Update TimeStamps in "normal" Table in a Temp-Table scenario

Question

In a table that use system versioning aka Temporal tables, is there a way to modify the time stamp columns (row start, row end, often named ValidFrom and ValidTo) some how?

It can be done in the temporal table itself when system versioning is off and the timestamps updates comply to the row-end-of-previuos-must-equal-row-start-of-next-pattern.

But it doesn't seems to work for the "normal" table itself, even if system versioning is off.

This works for the TT:

ALTER TABLE dbo.[Customer] SET (SYSTEM_VERSIONING = OFF);
update [dbo].[CustomerHistory] set ValidFrom = '2024-03-25 11:21:38.1854297'  where [Id] = 5
ALTER TABLE dbo.[Customer] SET (SYSTEM_VERSIONING = ON);

But it fails for the "normal" table

ALTER TABLE dbo.[Customer] SET (SYSTEM_VERSIONING = OFF);
update [dbo].[Customer] set ValidFrom = '2024-03-25 11:21:38.1854297'  where [Id] = 5
ALTER TABLE dbo.[Customer] SET (SYSTEM_VERSIONING = ON);

producing the error message: "The GENERATED ALWAYS columns in the "dbo.Customer" table cannot be updated."

Any way to get around it?

I need to copy a bunch of tables 1:1 from one DB to another, and the TimeStamps of the TT / History Tables need to be the ones from the original source database tables...

Answer 1

From the comments, it appears the real question is not how to UPDATE an ALWAYS GENERATED column, but how to migrate data from a temporal table into a new temporal table. If you need to copy data from a different (temporal) table to a new one, and retain the history dates, then create the new table(s) not as a temporal table and then ALTER it to one after you INSERT all the data. If you start by creating the new table as a temporal table then retaining the Start/End dates isn't possible, as you cannot INSERT into, or UPDATE the values of your GENERATED ALWAYS columns. See the linked duplicate (as this question was closed after this answer was added), on how to completely disable the GENERATED ALWAYS feature.

This means you create the table, and history tables, not as a temporal table. Such as like below (the CONSTRAINTs are important):

CREATE TABLE dbo.MyTemporalTable (ID int IDENTITY PRIMARY KEY,
                                  SomeString varchar(10) NULL,
                                  ValidFrom datetime2(7) NOT NULL CONSTRAINT DF_MyTemporalTable_ValidFrom DEFAULT SYSDATETIME(),
                                  ValidTo datetime2(7) NOT NULL CONSTRAINT DF_MyTemporalTable_ValidTo DEFAULT '9999-12-31T23:59:59.9999999')
GO
--Assumes existence of a history schema
CREATE TABLE history.MyTemporalTable (ID int NOT NULL,
                                      SomeString varchar(10) NULL,
                                      ValidFrom datetime2(7) NOT NULL,
                                      ValidTo datetime2(7) NOT NULL);

Now you INSERT all your data into these 2 tables. I'm just going to use some made up data here, it doesn't come from another table:

DECLARE @SomeArbitraryTime datetime2(7)= SYSDATETIME();

INSERT INTO dbo.MyTemporalTable (SomeString,ValidFrom)
VALUES('abc', DATEADD(DAY, -1, @SomeArbitraryTime)),
      ('def',@SomeArbitraryTime);
INSERT INTO history.MyTemporalTable (ID,
                                     SomeString,
                                     ValidFrom,
                                     ValidTo)
VALUES(2,'xyz',DATEADD(DAY, -1, @SomeArbitraryTime),@SomeArbitraryTime);

Now all the data is in the 2 tables, we can make the tables temporal:

ALTER TABLE dbo.MyTemporalTable ADD PERIOD FOR SYSTEM_TIME (ValidFrom,ValidTo);
GO
ALTER TABLE dbo.MyTemporalTable SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = history.MyTemporalTable));

And now we can do a couple of queries, and get the expected results:

SELECT *
FROM dbo.MyTemporalTable;

DECLARE @12HoursAgo datetime2(7) = DATEADD(HOUR, -12, SYSDATETIME());

SELECT *
FROM dbo.MyTemporalTable FOR SYSTEM_TIME AS OF @12HoursAgo;

Which returns the following 2 data sets:

ID	SomeString	ValidFrom	ValidTo
1	abc	2024-03-25 12:38:49.1272105	9999-12-31 23:59:59.9999999
2	def	2024-03-26 12:38:49.1272105	9999-12-31 23:59:59.9999999

ID	SomeString	ValidFrom	ValidTo
1	abc	2024-03-25 12:38:49.1272105	9999-12-31 23:59:59.9999999
2	xyz	2024-03-25 12:38:49.1272105	2024-03-26 12:38:49.1272105

---

Clean up:

ALTER TABLE dbo.MyTemporalTable SET (SYSTEM_VERSIONING = OFF);
GO
DROP TABLE dbo.MyTemporalTable;
DROP TABLE history.MyTemporalTable;

db<>fiddle