Modificar datos en una tabla temporal con control de versiones del sistema

Aplica a: SQL Server 2016 (13.x) y versiones posteriores Azure SQL Database Azure SQL Managed Instance Base de datos SQL en Microsoft Fabric

Los datos de una tabla temporal con control de versiones del sistema se modifican mediante instrucciones habituales del lenguaje de manipulación de datos (DML), aunque con una diferencia importante: no es posible modificar directamente los datos de las columnas de período. Cuando se actualizan los datos, se versionan y la versión anterior de cada fila actualizada se inserta en la tabla de historial. Cuando se eliminan datos, la eliminación es lógica y la fila se mueve a la tabla de historial desde la tabla actual; Los datos no se eliminan de forma permanente.

Insertar datos

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

Insertar nuevos datos con columnas de período visibles

Construya su INSERT sentencia teniendo en cuenta las columnas PERIOD visibles de la siguiente manera:

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

Insertar datos en una tabla con columnas de período ocultas

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 sigan funcionando al habilitar el versionado del sistema en las tablas que se benefician del versionado.

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 está particionada, puedes usarla PARTITION SWITCH para cargar datos eficientemente en una partición vacía, o para cargar en varias particiones en paralelo.

La tabla de almacenamiento provisional usada en la instrucción PARTITION SWITCH IN con una tabla temporal debe tener definido SYSTEM_TIME PERIOD, aunque 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 tabla de historial para el escenario de desastre. Sin embargo, no puedes actualizar las columnas PERIOD y no puedes actualizar directamente los datos 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 siguiente error.

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

Actualizar la tabla actual desde la tabla 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 sentencias mientras SYSTEM_VERSIONING = ON:

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

Uso MERGE para modificar datos en 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 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);

Comentarios

¿Le ha resultado útil esta página?

Last updated on 2026-08-18