Modificar dados em uma tabela temporal versionada pelo sistema

Aplica-se a: SQL Server 2016 (13.x) e versões posteriores Banco de Dados SQL do AzureInstância Gerenciada de SQL do AzureSQL database in Microsoft Fabric

Os dados em uma tabela temporal com versão controlada pelo sistema são modificados por meio de instruções regulares de DML (linguagem de manipulação de dados), com uma diferença importante: os dados da coluna de período não podem ser modificados diretamente. Quando os dados são atualizados, eles são versionados, e a versão anterior de cada linha atualizada é inserida na tabela de histórico. Quando os dados são excluídos, a exclusão é lógica, e a linha é movida para a tabela de histórico a partir da tabela atual; Os dados não são deletados permanentemente.

Inserir dados

Ao inserir novos dados, é necessário levar em conta as colunas PERIOD, caso elas não sejam HIDDEN. Você também pode usar a alternância de partições com tabelas temporais.

Inserir novos dados com colunas de período visíveis

Construa sua INSERT declaração para considerar as colunas visíveis PERIOD da seguinte forma:

Se você especificar a lista de colunas em sua instrução INSERT, poderá omitir as colunas PERIOD porque o sistema gera valores automaticamente para essas colunas.

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

Se você especificar as colunas PERIOD na lista de colunas na sua instrução INSERT, precisará especificar DEFAULT como seu valor.

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

Se você não especificar a lista de colunas em sua instrução INSERT, especifique DEFAULT para colunas PERIOD.

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

Inserir dados em uma tabela com colunas de período ocultas

Se as colunas PERIOD forem especificadas como HIDDEN, você não precisará considerar as colunas PERIOD em sua instrução INSERT. Esse comportamento garante que seus aplicativos herdados continuem funcionando quando você habilita o versionamento do sistema em tabelas que se beneficiam do versionamento.

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

Inserir dados usando PARTITION SWITCH

Se a tabela atual estiver particionada, você pode usar PARTITION SWITCH para carregar dados eficientemente em uma partição vazia, ou para carregar em múltiplas partições em paralelo.

A tabela de preparação usada na instrução PARTITION SWITCH IN com uma tabela temporal deve ter SYSTEM_TIME PERIOD definido, mas não precisa ser temporal. Isso garante que as verificações de consistência temporais são executadas durante a inserção de dados em uma tabela de preparo ou quando o período SYSTEM_TIME é adicionado a uma tabela de preparo preenchida previamente.

/* 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 você tentar realizar PARTITION SWITCH de uma tabela sem definição de período, receberá uma mensagem de erro:

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.

Atualizar dados

Você atualiza os dados na tabela atual usando uma instrução UPDATE comum. Você pode atualizar os dados na tabela atual a partir da tabela de histórico para o cenário de desastre. No entanto, não é possível atualizar as colunas PERIOD nem atualizar dados diretamente na tabela de histórico enquanto SYSTEM_VERSIONING = ON.

Se você definir SYSTEM_VERSIONING como OFF e atualizar linhas das tabelas atual e de histórico, o sistema não preservará o histórico de alterações.

Atualizar a tabela atual

Neste exemplo, a coluna ManagerID é atualizada para cada linha onde DeptID = 10. As colunas PERIOD não são referenciadas de forma alguma.

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

No entanto, você não pode atualizar uma coluna PERIOD e não pode atualizar a tabela de histórico. Neste exemplo, uma tentativa de atualizar uma coluna PERIOD gera um erro.

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

A instrução gera o seguinte erro.

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

Atualizar a tabela atual com base na tabela de histórico

Você pode usar UPDATE na tabela atual para reverter o estado real da linha para um estado válido em um ponto específico no passado. Pense nisso como uma reversão para uma última boa versão de linha conhecida. O exemplo a seguir mostra a reversão para os valores na tabela de histórico em 25/4/2015, onde o valor de 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;

Excluir dados

Você exclui dados da tabela atual com uma instrução DELETE comum. A coluna do período final para linhas excluídas é preenchida com a hora de início da transação subjacente. Não é possível excluir linhas diretamente da tabela de histórico enquanto SYSTEM_VERSIONING é ON. Se você definir SYSTEM_VERSIONING = OFF e excluir linhas das tabelas atual e de histórico, o sistema não preservará o histórico de alterações.

As instruções a seguir não têm suporte enquanto SYSTEM_VERSIONING = ON:

  • TRUNCATE
  • SWITCH PARTITION OUT para a tabela atual
  • SWITCH PARTITION IN para a tabela de histórico

Usar MERGE para modificar dados na tabela temporal

A operação MERGE é suportada com as mesmas limitações que as instruções INSERT e UPDATE têm no que diz respeito às colunas 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);

Conteúdo relacionado

  • Last updated on