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