Compartilhar via

Modificar dados em uma tabela temporal com controle de versão de sistema

  • Artigo

Aplica-se a: SQL Server 2016 (13.x) e posterior Banco de Dados SQL do Azure Instância Gerenciada de SQL do Azure

Os dados em uma tabela temporal com controle da 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 de coluna do período não podem ser modificados diretamente. Quando os dados são atualizados, eles recebem controle de versão e a versão anterior de cada linha atualizada é inserida na tabela de histórico. Quando dados são excluídos, a exclusão é lógica: a linha é movida da tabela atual para a tabela de histórico. Ela não é excluída 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 alternância de partição 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 da seguinte maneira para considerar as novas colunas PERIOD:

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 HIDDEN

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 a funcionar quando você habilitar o controle de versão do sistema em tabelas que se beneficiam disso.

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

A tabela de preparo usada na instrução PARTITION SWITCH IN com uma tabela temporal deve ter sido definida em SYSTEM_TIME PERIOD, mas não precisa ser uma tabela 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

Atualize os dados na tabela atual com uma instrução UPDATE regular. Você pode atualizar dados na tabela atual 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 a seguir gera o erro a seguir.

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

Atualizar a tabela atual 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 passado. Pense nisso como reverter para uma última versão de linha reconhecidamente boa. 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 os dados

Exclua os dados na tabela atual com uma instrução DELETE regular. 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 são aceitas enquanto SYSTEM_VERSIONING = ON:

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

Use MERGE para modificar dados em tabela temporal

A operação MERGE é válida com as mesmas limitações que as instruções INSERT e UPDATE em relação a 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);

Conteúdo relacionado