Modifica dei dati in una tabella temporale con versione di sistema

Si applica a: SQL Server 2016 (13.x) e versioni successive database SQL di Azure AzureSQL Managed InstanceSQL database in Microsoft Fabric

È possibile modificare i dati in una tabella temporale versione di sistema usando normali istruzioni Data Manipulation Language (DML), ma i dati della colonna periodo non possono essere modificati direttamente. Quando i dati vengono aggiornati, vengono versionati e la versione precedente di ogni riga aggiornata viene inserita nella tabella storica. Quando i dati vengono eliminati, la cancellazione è logica e la riga viene spostata nella tabella storica dalla tabella corrente; I dati non vengono cancellati in modo permanente.

Inserisci i dati

Quando si inseriscono nuovi dati è necessario tenere conto delle colonne PERIOD se non sono HIDDEN. È possibile utilizzare la commutazione delle partizioni anche con le tabelle temporali.

Inserire nuovi dati con colonne periodo visibili

Costruisci la tua INSERT dichiarazione per tenere conto delle colonne visibili PERIOD come segue:

Se si specifica l'elenco delle colonne nell’istruzione INSERT, è possibile omettere le colonne PERIOD, perché il sistema genera automaticamente i relativi valori.

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

Se si specificano le colonne PERIOD nell'elenco colonne nell’istruzione INSERT, è necessario specificare anche DEFAULT come relativo valore.

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

Se non si specifica l'elenco colonne nell’istruzione INSERT, specificare DEFAULT per le colonne PERIOD.

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

Inserire dati in una tabella con colonne periodo NASCOSTE

Se le colonne PERIOD vengono specificate come HIDDEN, non è necessario tenere conto delle colonne PERIOD nell'istruzione INSERT. Questo comportamento assicura che le tue applicazioni legacy continuino a funzionare quando abiliti la versionizzazione di sistema sulle tabelle che beneficiano della versionizzazione.

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');

Inserire i dati con PARTITION SWITCH

Se la tabella corrente è partizionata, puoi usarla PARTITION SWITCH per caricare dati in modo efficiente in una partizione vuota, oppure per caricarli in più partizioni in parallelo.

La tabella di staging utilizzata nell'istruzione PARTITION SWITCH IN con una tabella temporale deve avere SYSTEM_TIME PERIOD definito, ma non deve necessariamente essere una tabella temporale. Ciò assicura l’esecuzione di controlli di coerenza temporale durante l'inserimento di dati in una tabella di staging o quando il periodo SYSTEM_TIME viene aggiunto a una tabella di staging già popolata.

/* 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;

Se si tenta di eseguire l'istruzione PARTITION SWITCH da una tabella priva di definizione del periodo verrà visualizzato il messaggio di errore:

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.

Aggiornamento dei dati

I dati nella tabella corrente vengono aggiornati con una normale istruzione UPDATE. È possibile aggiornare i dati nella tabella corrente dalla tabella di cronologia per lo scenario problematico. Non è tuttavia possibile aggiornare le colonne PERIOD né aggiornare direttamente i dati nella tabella di cronologia se SYSTEM_VERSIONING = ON.

Se si imposta SYSTEM_VERSIONING su OFF e si aggiornano le righe dalle tabelle correnti e di cronologia, il sistema non mantiene la cronologia delle modifiche.

Aggiornare la tabella corrente

In questo esempio, la colonna ManagerID viene aggiornata per ogni riga in cui DeptID è 10. Non c’è alcun riferimento alle colonne PERIOD.

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

Tuttavia, non è possibile aggiornare una colonna PERIOD né la tabella di cronologia. In questo esempio, il tentativo di aggiornare una colonna PERIOD genera un errore.

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

L'istruzione genera il seguente errore.

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

Aggiornare la tabella corrente dalla tabella di cronologia

È possibile usare l'istruzione UPDATE nella tabella corrente per ripristinare lo stato corrente della riga a uno specifico stato valido precedente. Si pensi a questo come tornare alla ultima versione di riga nota. Nell'esempio seguente i valori nella tabella di cronologia vengono ripristinati al 25 aprile 2015, quando DeptID è 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;

Eliminare dati

È possibile eliminare dati dalla tabella corrente con una normale istruzione DELETE. La colonna periodo finale delle righe eliminate è popolata con l'ora di inizio della transazione sottostante. Non è possibile eliminare direttamente le righe dalla tabella di cronologia se SYSTEM_VERSIONING è ON. Se si impostano SYSTEM_VERSIONING = OFF e si eliminano righe dalle tabelle correnti e di cronologia, il sistema non mantiene la cronologia delle modifiche.

Le seguenti istruzioni non sono supportate se SYSTEM_VERSIONING = ON:

  • TRUNCATE
  • SWITCH PARTITION OUT per la tabella corrente
  • SWITCH PARTITION IN per la tabella di cronologia

Usare MERGE per modificare i dati nella tabella temporale

L’operazione MERGE è supportata con le stesse limitazioni delle istruzioni INSERT e UPDATE relativamente alle colonne PERIOD.

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

Contenuti correlati

  • Last updated on