Dela via

Ändra data i en systemversionsbaserad temporal tabell

  • Artikel

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 de versionshanterade och den tidigare versionen av varje uppdaterad rad infogas i historiktabellen. När data tas bort är borttagningen logisk och raden flyttas till historiktabellen från den aktuella tabellen. data tas inte bort 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

Du kan skapa din INSERT-instruktion när du har synliga PERIOD kolumner på följande sätt för att ta hänsyn till de PERIOD kolumnerna:

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 som en effektiv mekanism för att läsa in data till en tom partition eller läsa in dem i flera partitioner parallellt.

Mellanlagringstabellen som används i PARTITION SWITCH IN-instruktionen med en temporal tabell måste ha SYSTEM_TIME PERIOD definierad, men den behöver inte vara en temporal tabell. Detta säkerställer att kontroller av temporal konsekvens utförs under datainfogningen 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 data

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ända 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 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