Issues in SYSTEM_VERSIONING OFF - Insert record failed in SQL Server

839 views Asked by At

I'm having a System Versioned Temporal Table namely [dbo].[Contact], for development purpose I tried to seed some old dated data. The table contains GENERATED ALWAYS column.

This question is a Child question of my exiting question Seed data with old dates in Temporal Table - SQL Server

Original Table Schema:

CREATE TABLE [dbo].[Contact](
    [ContactID] [uniqueidentifier] NOT NULL,
    [ContactNumber] [nvarchar](50) NOT NULL,
    [SysStartTime] [datetime2](0) GENERATED ALWAYS AS ROW START NOT NULL,
    [SysEndTime] [datetime2](0) GENERATED ALWAYS AS ROW END NOT NULL,
 CONSTRAINT [PK_Contact] PRIMARY KEY NONCLUSTERED 
(
    [ContactID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
    PERIOD FOR SYSTEM_TIME ([SysStartTime], [SysEndTime])
) ON [PRIMARY]
WITH
(
    SYSTEM_VERSIONING = ON (HISTORY_TABLE = [dbo].[ContactHistory] , DATA_CONSISTENCY_CHECK = ON )
)

enter image description here

I tried to switch it OFF the SYSTEM_VERSIONING, by using the following SQL Code

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

Now the Table becomes like a normal table

enter image description here

Now I tried to Insert a record in [dbo].[Contact]

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
)

But Still I'm getting the same Error

Msg 13536, Level 16, State 1, Line 20 Cannot insert an explicit value into a GENERATED ALWAYS column in table 'DevDB.dbo.Contact'. Use INSERT with a column list to exclude the GENERATED ALWAYS column, or insert a DEFAULT into GENERATED ALWAYS column.

0

There are 0 answers