Seed Data with Old Dates in Temporal Table - SQL Server

Seed data with old dates in Temporal Table - SQL Server

Finally I found a solution

Step #1: Need to Switch it OFF the SYSTEM_VERSIONING

ALTER TABLE dbo.Contact SET (SYSTEM_VERSIONING = OFF);

Step #2: Need to drop the PERIOD FOR SYSTEM_TIME

ALTER TABLE dbo.Contact DROP PERIOD FOR SYSTEM_TIME

Step #3: Insert required record with past date

INSERT INTO dbo.Contact
(
    ContactID,
    ContactNumber,
    SysStartTime,
    SysEndTime
)
VALUES
(
    NEWID(), -- ContactID - uniqueidentifier
    N'1234567890', -- ContactNumber - nvarchar
    '2014-09-13 00:00:00', -- SysStartTime - datetime2
    '9999-12-31 23:59:59' -- SysEndTime - datetime2
)

Step #4: Need to Add the PERIOD FOR SYSTEM_TIME

ALTER TABLE dbo.Contact
ADD PERIOD FOR SYSTEM_TIME ([SysStartTime], [SysEndTime])

Step #5: Need to Switch it ON the SYSTEM_VERSIONING

ALTER TABLE dbo.[Contact] SET (SYSTEM_VERSIONING = ON
 (HISTORY_TABLE=dbo.[ContactHistory],DATA_CONSISTENCY_CHECK=ON)
);

That's it...

Backfill a temporal table

You don't need to change the server time to have the correct time.

The process is described below.

1. Create all history tables first for each table (same structure as if you create temporal table with default history table).
2. Create indexes for all history table (same structure as if you create temporal table with default history table).
3. Populate the history tables directly, including SysStartTime, SysEndTime columns with desired datetime values
4. Alter tables to enable versioning, specifying history table.

https://learn.microsoft.com/en-us/sql/relational-databases/tables/creating-a-system-versioned-temporal-table

(see Alter Non-Temporal Table to be System-Versioned Temporal Table paragraph)

Alternatively, you can:

1. Drop and recreate all tables with enabled versioning (specifying your own (non-default) history tables).
2. Run ETLs
3. Stop versioning temporary
4. Update history tables to have correct values for SysStartTime/SysEndTime
5. Re-enable versioning (specifying existing history tables)

Make a normal table as temporal table

Create the new table address_user_new, insert the data, then use sp_rename to rename address_user to address_user_old and address_user_new to address_user. This can all be done in a transaction to ensure ensure that the transition is atomic and apparently-instantaneous. eg

if object_id('address_user') is not null
  ALTER TABLE address_user SET ( SYSTEM_VERSIONING = OFF)
go
if object_id('address_user_new') is not null
  ALTER TABLE address_user_new SET ( SYSTEM_VERSIONING = OFF)
go
drop table if exists address_user
drop table if exists address_user_history
drop table if exists address_user_new
drop table if exists address_user_old
go

CREATE TABLE address_user 
(
    [username] VARCHAR(13) NOT NULL,
    [address] CHAR(58) NOT NULL,
    [id] BIGINT NOT NULL,

    CONSTRAINT [PK_address_user] 
        PRIMARY KEY CLUSTERED ([id] ASC)
);

go
CREATE TABLE address_user_new 
(
    [username] VARCHAR(13) NOT NULL,
    [address] CHAR(58) NOT NULL,
    [id] BIGINT NOT NULL,
    [sys_start_time] DATETIME2(7) 
        GENERATED ALWAYS AS ROW START HIDDEN NOT NULL,
    [sys_end_time] DATETIME2 (7) 
        GENERATED ALWAYS AS ROW END HIDDEN NOT NULL,

    PERIOD FOR SYSTEM_TIME ([sys_start_time], [sys_end_time]),
    CONSTRAINT [PK_address_user_new] 
        PRIMARY KEY CLUSTERED ([id] ASC)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE=[dbo].[address_user_history], DATA_CONSISTENCY_CHECK=ON));

go

set xact_abort on
begin transaction

insert into address_user_new(username,address,id)
select username,address,id
from address_user with (tablockx)

exec sp_rename 'address_user', 'address_user_old', 'OBJECT'
exec sp_rename 'PK_address_user', 'PK_address_user_old', 'OBJECT'

exec sp_rename 'address_user_new', 'address_user', 'OBJECT'
exec sp_rename 'PK_address_user_new', 'PK_address_user', 'OBJECT'

commit transaction

Temporal table default constraint for ValidFrom does not work

I got with column 'ValidFrom' as '2017-08-09 11:18:30'. Why I do not get the default value '1900-01-01 00:00:00' for that column as expected?

It is working as expected. Your assumptions are wrong. From Temporal Tables:

INSERTS:

On an INSERT, the system sets the value for the SysStartTime column to the begin time of the current transaction (in the UTC time zone) based on the system clock and assigns the value for the SysEndTime column to the maximum value of 9999-12-31. This marks the row as open.

How can I generate a temporary table filled with dates in SQL Server 2000?

This will quickly populate a table with 170 years worth of dates.

CREATE TABLE CalendarMonths (
  date DATETIME,
  PRIMARY KEY (date)
)

DECLARE
  @basedate DATETIME,
  @offset   INT
SELECT
  @basedate = '01 Jan 2000',
  @offset = 1

WHILE (@offset < 2048)
BEGIN
  INSERT INTO CalendarMonths SELECT DATEADD(MONTH, @offset, date) FROM CalendarMonths
  SELECT @offset = @offset + @offset
END

You can then use it by LEFT joining on to that table, for the range of dates you require.

---

Related Topics