3

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.

B.Balamanigandan
  • 4,713
  • 11
  • 68
  • 130
  • I think you are missing a point of system-versioned table here. You did not insert the rows into the table created today back in 2014, did you? If you want to preserve this date I would create a separate column for it and leave the automatically generated as they are to indicate when you actually inserted the data into the table. – PacoDePaco Sep 13 '17 at 10:54
  • @PawełKucharski I'm in the QA Testing, the actual code and stored procedures are using this `SysStartTime`, for testing periodic search I need some data in my table. I don't have privilege to modify the structure. – B.Balamanigandan Sep 13 '17 at 10:58
  • Did you find a solution? I need to copy history from another table but hit with the same problem. – Gao Shenghan Jan 30 '20 at 23:42

0 Answers0