Modificar dados em uma tabela temporal versionada pelo sistema

Aplica-se a: SQL Server 2016 (13.x) e versões posteriores Azure SQL Database AzureSQL Managed Instance SQL database in Microsoft Fabric

Os dados em uma tabela temporal com versão do sistema são modificados usando instruções DML (linguagem de manipulação de dados regulares), 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 da tabela atual para uma tabela de histórico; os dados não são excluídos permanentemente.

Inserir dados

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

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

Você pode construir sua instrução INSERT quando tiver colunas PERIOD visíveis como se segue, de forma a levar em consideração as colunas PERIOD.

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

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

Se especificares as colunas PERIOD na lista de colunas na tua instrução INSERT, então necessitarás especificar DEFAULT como valor delas.

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

Se não especificares a lista de colunas na tua instrução INSERT, especifica DEFAULT para as 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 PERIOD colunas forem especificadas como HIDDEN, não é necessário contabilizar as colunas PERIOD na instrução INSERT. Esse comportamento garante que seus aplicativos herdados continuem a funcionar quando você habilita o controle de versão do sistema em tabelas que se beneficiam do controle de versão.

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ê poderá usáPARTITION SWITCH como um mecanismo eficiente para carregar dados em uma partição vazia ou para carregar em várias partições em paralelo.

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

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

Caso tente executar PARTITION SWITCH de uma tabela sem uma definição de período, irá 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 com uma instrução regular UPDATE. Você pode atualizar os dados na tabela atual utilizando a tabela de histórico para o cenário de desastre. No entanto, não se pode atualizar colunas PERIOD nem atualizar diretamente os dados na tabela de histórico enquanto estiver SYSTEM_VERSIONING = ON.

Se você definir SYSTEM_VERSIONING para 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 em que o DeptID é 10. As colunas PERIOD não são referenciadas de forma alguma.

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

No entanto, não é possível atualizar uma coluna PERIOD e não é possível 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 a partir da 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 tempo no passado. Pense nisso como voltar a uma última boa versão conhecida da linha. O exemplo a seguir mostra a reversão para os valores na tabela de histórico a partir de 25 de abril de 2015, onde o 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;

Eliminar dados

Você exclui dados na tabela atual com uma instrução DELETE regular. A coluna de período final para linhas excluídas é preenchida com a hora de início da transação subjacente. Não é possível excluir diretamente linhas da tabela de histórico enquanto SYSTEM_VERSIONING estiver 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 seguintes instruções não são suportadas durante o SYSTEM_VERSIONING = ON:

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

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, em relação à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 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);

Comentários

Esta página foi útil?

Last updated on 2025-11-18