Ändra data i en systemversionsbaserad temporal tabell

Gäller för: SQL Server 2016 (13.x) och senare versioner Azure SQL DatabaseAzure SQL Managed InstanceSQL-databas i Microsoft Fabric

Data i en systemversionsbaserad temporal tabell ändras med hjälp av vanliga DML-instruktioner (datamanipuleringsspråk), med en viktig skillnad: periodkolumndata kan inte ändras direkt. När data uppdateras är den versionsbaserad och den tidigare versionen av varje uppdaterad rad infogas i historiktabellen. När data raderas är borttagningen logisk, och raden flyttas från den aktuella tabellen till historiktabellen; Datan raderas inte permanent.

Infoga data

När du infogar nya data måste du ta hänsyn till PERIOD-kolumnerna om de inte är HIDDEN. Du kan också använda partitionsväxling med temporala tabeller.

Infoga nya data med synliga periodkolumner

Utforma ditt INSERT-uttryck med hänsyn till de synliga PERIOD kolumnerna enligt följande:

Om du anger kolumnlistan i INSERT-instruktionen kan du utelämna de PERIOD kolumnerna eftersom systemet genererar värden för dessa kolumner automatiskt.

-- Insert with column list and without period columns
INSERT INTO [dbo].[Department]
(
    [DeptID],
    [DeptName],
    [ManagerID],
    [ParentDeptID]
)
VALUES (10, 'Marketing', 101, 1);

Om du anger de PERIOD kolumnerna i kolumnlistan i INSERT-instruktionen måste du ange DEFAULT som deras värde.

INSERT INTO [dbo].[Department]
(
    DeptID,
    DeptName,
    ManagerID,
    ParentDeptID,
    ValidFrom,
    ValidTo
)
VALUES (11, 'Sales', 101, 1, DEFAULT, DEFAULT);

Om du inte anger kolumnlistan i INSERT-instruktionen anger du DEFAULT för PERIOD kolumner.

-- Insert without a column list and DEFAULT values for period columns
INSERT INTO [dbo].[Department]
VALUES (
    12,
    'Production',
    101,
    1,
    DEFAULT,
    DEFAULT
);

Infoga data i en tabell med dolda periodkolumner

Om PERIOD kolumner anges som HIDDENbehöver du inte ta hänsyn till de PERIOD kolumnerna i INSERT-instruktionen. Det här beteendet garanterar att dina äldre program fortsätter att fungera när du aktiverar systemversionshantering på tabeller som drar nytta av versionshantering.

CREATE TABLE [dbo].[CompanyLocation]
(
    [LocID] INT IDENTITY (1, 1) NOT NULL PRIMARY KEY,
    [LocName] VARCHAR (50) NOT NULL,
    [City] VARCHAR (50) NOT NULL,
    [ValidFrom] DATETIME2 GENERATED ALWAYS AS ROW START HIDDEN NOT NULL,
    [ValidTo] DATETIME2 GENERATED ALWAYS AS ROW END HIDDEN NOT NULL,
    PERIOD FOR SYSTEM_TIME ([ValidFrom], [ValidTo])
)
WITH (SYSTEM_VERSIONING = ON);
GO

INSERT INTO [dbo].[CompanyLocation]
VALUES ('Headquarters', 'New York');

Infoga data med PARTITIONSVÄXEL

Om den aktuella tabellen är partitionerad kan du använda PARTITION SWITCH den för att effektivt ladda data till en tom partition, eller ladda in i flera partitioner parallellt.

Stagingtabellen som används i satsen PARTITION SWITCH IN med en temporal tabell måste ha SYSTEM_TIME PERIOD definierats, men den behöver inte vara en temporal tabell. Detta säkerställer att temporala konsekvenskontroller utförs under införandet av data i en mellanlagringstabell eller när SYSTEM_TIME period läggs till i en fördefinierad mellanlagringstabell.

/* Create staging table with period definition for SWITCH IN temporal table */
CREATE TABLE [dbo].[Staging_Department_Partition2]
(
    [DeptID] INT NOT NULL,
    [DeptName] VARCHAR (50) NOT NULL,
    [ManagerID] INT NULL,
    [ParentDeptID] INT NULL,
    [ValidFrom] DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL,
    [ValidTo] DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL,
    PERIOD FOR SYSTEM_TIME ([ValidFrom], [ValidTo])
) ON [PRIMARY];

/* Create aligned primary key */
ALTER TABLE [dbo].[Staging_Department_Partition2]
    ADD CONSTRAINT [Staging_Department_Partition2_PK]
        PRIMARY KEY CLUSTERED ([DeptID] ASC) ON [PRIMARY];

/*
Create and enforce constraints for partition boundaries.
Partition 2 contains rows with DeptID > 100 and DeptID <=200
*/
ALTER TABLE [dbo].[Staging_Department_Partition2] WITH CHECK
    ADD CONSTRAINT [chk_staging_Department_partition_2]
            CHECK ([DeptID] > N'100' AND [DeptID] <= N'200');

ALTER TABLE [dbo].[Staging_Department_Partition2]
    CHECK CONSTRAINT [chk_staging_Department_partition_2];

/*Load data into staging table*/
INSERT INTO [dbo].[staging_Department] (
    [DeptID],
    [DeptName],
    [ManagerID],
    [ParentDeptID]
)
VALUES (101, 'D101', 1, NULL);

/*Use PARTITION SWITCH IN to efficiently add data to current table */
ALTER TABLE [Staging_Department]
    SWITCH TO [dbo].[Department] PARTITION 2;

Om du försöker utföra PARTITION SWITCH från en tabell utan en perioddefinition får du ett felmeddelande:

Msg 13577, Level 16, State 1, Line 25 ALTER TABLE SWITCH statement failed on table 'MyDB.dbo.Staging_Department_2015_09_26' because target table has SYSTEM_TIME PERIOD while source table does not have it.

Uppdatera datainformation

Du uppdaterar data i den aktuella tabellen med en vanlig UPDATE-instruktion. Du kan uppdatera data i den aktuella tabellen från historiktabellen för katastrofscenariot. Du kan dock inte uppdatera PERIOD kolumner och du kan inte uppdatera data direkt i historiktabellen när SYSTEM_VERSIONING = ON.

Om du anger SYSTEM_VERSIONING till OFF och uppdaterar rader från de aktuella tabellerna och historiktabellerna bevarar systemet inte historiken för ändringar.

Uppdatera den aktuella tabellen

I det här exemplet uppdateras kolumnen ManagerID för varje rad där DeptID är 10. De PERIOD kolumnerna refereras inte på något sätt.

UPDATE [dbo].[Department]
SET [ManagerID] = 501
WHERE [DeptID] = 10;

Du kan dock inte uppdatera en PERIOD kolumn och du kan inte uppdatera historiktabellen. I det här exemplet genererar ett försök att uppdatera en PERIOD kolumn ett fel.

UPDATE [dbo].[Department]
    SET ValidFrom = '2015-09-23 23:48:31.2990175'
WHERE DeptID = 10;

Instruktionen genererar följande fel.

Msg 13537, Level 16, State 1, Line 3
Cannot update GENERATED ALWAYS columns in table 'TmpDev.dbo.Department'.

Uppdatera den aktuella tabellen från historiktabellen

Du kan använda UPDATE i den aktuella tabellen för att återställa det faktiska radtillståndet till ett giltigt tillstånd vid en viss tidpunkt tidigare. Tänk på detta som att återgå till en senaste kända radversionen. I följande exempel visas hur du återgår till värdena i historiktabellen från och med den 25 april 2015, där DeptID är 10.

UPDATE Department
SET DeptName = History.DeptName
FROM Department FOR SYSTEM_TIME
    AS OF '2015-04-25' AS History
WHERE History.DeptID = 10
    AND Department.DeptID = 10;

Ta bort data

Du tar bort data i den aktuella tabellen med en vanlig DELETE-instruktion. Kolumnen slutperiod för borttagna rader fylls i med starttiden för den underliggande transaktionen. Du kan inte ta bort rader direkt från historiktabellen när SYSTEM_VERSIONING är ON. Om du anger SYSTEM_VERSIONING = OFF och tar bort rader från de aktuella tabellerna och historiktabellerna bevarar systemet inte historiken för ändringar.

Följande påståenden stöds inte medan SYSTEM_VERSIONING = ON:

  • TRUNCATE
  • SWITCH PARTITION OUT för den aktuella tabellen
  • SWITCH PARTITION IN för historiktabellen

Använd MERGE för att ändra data i tidstabellen

Åtgärden MERGE stöds med samma begränsningar som INSERT- och UPDATE-instruktioner har när det gäller PERIOD kolumner.

CREATE TABLE DepartmentStaging
(
    DeptId INT,
    DeptName VARCHAR (50)
);
GO

INSERT INTO DepartmentStaging
VALUES (1, 'Company Management');

INSERT INTO DepartmentStaging
VALUES (10, 'Science & Research');

INSERT INTO DepartmentStaging
VALUES (15, 'Process Management');

MERGE INTO dbo.Department AS target
USING (
    SELECT DeptId, DeptName
    FROM DepartmentStaging
    ) AS source(DeptId, DeptName)
    ON (target.DeptId = source.DeptId)
WHEN MATCHED
    THEN UPDATE SET DeptName = source.DeptName
WHEN NOT MATCHED
    THEN
        INSERT (DeptId, DeptName)
        VALUES (source.DeptId, source.DeptName);

Relaterat innehåll

  • Last updated on