Compartir a través de

Modificación de los datos de una tabla temporal con control de versiones del sistema

  • Artículo

Se aplica a: SQL Server 2016 (13.x) y versiones posteriores Azure SQL Database Azure SQL Managed Instance

Los datos de una tabla temporal con control de versiones del sistema se modifican mediante instrucciones de lenguaje de manipulación de datos (DML) periódicas con una diferencia importante: no es posible modificar directamente los datos de las columnas de periodo. Al actualizar datos, se crea una versión y la instancia antigua de cada fila actualizada se inserta en la tabla de historial. Al eliminar datos, la eliminación es lógica, y la fila se mueve a la tabla de historial desde la actual, los datos no se eliminan de manera permanente.

Insertar datos

Al insertar nuevos datos, debe tener en cuenta las columnas PERIOD si no son HIDDEN. También puede utilizar la modificación de la partición con las tablas temporales.

Inserción de nuevos datos con columnas PERIOD visibles

Puede crear la instrucción INSERT cuando tenga columnas PERIOD visibles como se muestra a continuación, para tener en cuenta las columnas PERIOD:

Si especifica la lista de columnas en la instrucción INSERT, puede omitir las columnas PERIOD porque el sistema genera automáticamente valores para ellas.

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

Si especifica las columnas PERIOD en la lista de columnas en la instrucción INSERT, tendrá que especificar DEFAULT como su valor.

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

Si no se especifica la lista de columnas en la instrucción INSERT, especifique DEFAULT para las columnas PERIOD.

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

Inserción de datos en una tabla con columnas PERIOD HIDDEN

Si las columnas PERIOD se especifican como HIDDEN, no es necesario tener en cuenta las columnas PERIOD de la instrucción INSERT. Este comportamiento garantiza que las aplicaciones heredadas seguirán funcionando al habilitar el control de versiones del sistema en las tablas en las que resulta provechoso.

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

Inserción de datos mediante PARTITION SWITCH

Si la tabla actual tiene particiones, puede utilizar PARTITION SWITCH como un mecanismo eficaz para cargar datos en una partición vacía o en varias particiones en paralelo.

La tabla de almacenamiento provisional que se usa en la instrucción PARTITION SWITCH IN con una tabla temporal debe tener definida una instancia de SYSTEM_TIME PERIOD, pero no es necesario que sea una tabla temporal. Así se garantiza la realización de comprobaciones de coherencia durante la inserción de datos en una tabla de almacenamiento provisional o cuando se agregue el periodo SYSTEM_TIME a una tabla de almacenamiento provisional rellenada 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;

Si trata de ejecutar PARTITION SWITCH desde una tabla sin una definición de periodo, se genera un mensaje de error:

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.

Actualización de datos

Puede actualizar datos de la tabla actual con una instrucción UPDATE convencional. Puede actualizar los datos de la tabla actual desde la de historial para el escenario de error. Pero no puede actualizar columnas PERIOD ni datos directamente en la tabla de historial mientras SYSTEM_VERSIONING = ON.

Si establece SYSTEM_VERSIONING en OFF y actualiza filas de las tablas actuales y de historial, el sistema no conserva el historial de cambios.

Actualización de la tabla actual

En este ejemplo, se actualiza la columna ManagerID para cada fila donde DeptID es 10. No se hace referencia a las columnas PERIOD de ninguna manera.

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

Pero no puede actualizar una columna PERIOD ni la tabla de historial. En este ejemplo, un intento de actualizar una columna PERIOD genera un error.

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

La instrucción genera el error siguiente.

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

Actualización de la tabla actual desde la de historial

Puede usar UPDATEen la tabla actual para revertir el estado real de la fila a uno válido en un momento dado en el pasado. Imagine que es como revertir a una última versión de fila conocida. En el siguiente ejemplo se muestra la reversión a los valores de la tabla de historial a partir del 25 de abril de 2015, donde DeptID es 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;

Eliminación de datos

Puede eliminar datos en la tabla actual con una instrucción DELETE convencional. La columna de periodo final de las filas eliminadas se rellena con la hora de inicio de la transacción subyacente. No puede eliminar filas directamente de la tabla de historial mientras SYSTEM_VERSIONING sea ON. Si establece SYSTEM_VERSIONING = OFF y elimina filas de las tablas actual y de historial, el sistema no conserva el historial de cambios.

No se admiten las siguientes instrucciones cuando SYSTEM_VERSIONING = ON:

  • TRUNCATE
  • SWITCH PARTITION OUT para la tabla actual
  • SWITCH PARTITION IN para la tabla de historial

Uso de MERGE para modificar los datos de la tabla temporal

La operación MERGE se admite con las mismas limitaciones que tienen las instrucciones INSERT y UPDATE, en lo relativo a las columnas 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);

Contenido relacionado