SET IDENTITY_INSERT (Transact-SQL)

Se aplica a:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsBase de datos de Azure SQL en Microsoft Fabric

Usando esta sentencia, puedes insertar valores explícitos en la IDENTITY columna de una tabla.

Este artículo y la IDENTITY sintaxis varían según las plataformas del SQL Motor de base de datos. Para Microsoft Fabric Data Warehouse, selecciona Fabric Data Warehouse en la lista desplegable de versiones.

Convenciones de sintaxis de Transact-SQL

Sintaxis

SET IDENTITY_INSERT [ [ database_name . ] schema_name . ] table_name { ON | OFF }

Argumentos

database_name

El nombre de la base de datos donde se encuentra la tabla especificada.

schema_name

El nombre del esquema que contiene la tabla.

table_name

Nombre de una tabla con una columna de identidad.

Observaciones

En cualquier momento, solo una tabla de una sesión puede tener la propiedad IDENTITY_INSERT establecida en ON. Si una tabla ya tiene esta propiedad configurada como ON, y emites una SET IDENTITY_INSERT ON instrucción para otra tabla, SQL Server devuelve un mensaje de error que indica SET IDENTITY_INSERT ya ONes , y reporta la tabla para la cual ON está establecida.

  • Cuando el argumento increment de la IDENTITY función es positivo y el valor insertado es mayor que el valor de identidad actual de la tabla, el SQL Motor de base de datos utiliza automáticamente el nuevo valor insertado como el valor de identidad actual.
  • Cuando el argumento increment de la IDENTITY función es negativo y el valor insertado es menor que el valor de identidad actual de la tabla, SQL Server utiliza automáticamente el nuevo valor insertado como el valor de identidad actual.

La configuración de SET IDENTITY_INSERT se establece en tiempo de ejecución o ejecución y no en tiempo de análisis.

Permisos

Debes ser dueño de la mesa o tener ALTER permiso para que la ponga.

Ejemplos

El ejemplo siguiente crea una tabla con una columna de identidad y muestra cómo se puede utilizar la opción SET IDENTITY_INSERT para rellenar un vacío en los valores de identidad causado por una instrucción DELETE.

USE AdventureWorks2022;
GO

Crear tabla de herramientas.

CREATE TABLE dbo.Tool
(
    ID INT IDENTITY NOT NULL PRIMARY KEY,
    Name VARCHAR (40) NOT NULL
);
GO

Insertar valores en la tabla de productos.

INSERT INTO dbo.Tool (Name)
VALUES ('Screwdriver'),
    ('Hammer'),
    ('Saw'),
    ('Shovel');
GO

Cree una brecha en los valores de identidad.

DELETE dbo.Tool
WHERE Name = 'Saw';
GO

SELECT *
FROM dbo.Tool;
GO

Intente insertar un valor de identificador explícito de 3.

INSERT INTO dbo.Tool (ID, Name)
VALUES (3, 'Garden shovel');
GO

El código anterior INSERT devuelve el siguiente error:

An explicit value for the identity column in table 'AdventureWorks2022.dbo.Tool' can only be specified when a column list is used and IDENTITY_INSERT is ON.

Establezca IDENTITY_INSERT en ON.

SET IDENTITY_INSERT dbo.Tool ON;
GO

Intente insertar un valor de identificador explícito de 3.

INSERT INTO dbo.Tool (ID, Name)
VALUES (3, 'Garden shovel');
GO

SELECT *
FROM dbo.Tool;
GO

Quitar tabla de herramientas.

DROP TABLE dbo.Tool;
GO

Contenido relacionado

Se aplica a:Warehouse en Microsoft Fabric

Úsalo SET IDENTITY_INSERT para insertar valores explícitos en la IDENTITY columna de una tabla en Fabric Data Warehouse. Utilízalo SET IDENTITY_INSERT cuando necesites insertar valores específicos en una columna de identidad, como durante la migración de datos, recuperación ante desastres o al poblar valores centinela en tablas dimensionales.

Convenciones de sintaxis de Transact-SQL

Sintaxis

SET IDENTITY_INSERT [ schema_name. ] table_name { ON | OFF }

Argumentos

schema_name

El nombre del esquema que contiene la tabla.

table_name

Nombre de una tabla con una columna de identidad.

Observaciones

En cualquier momento, solo una tabla de una sesión puede tener la propiedad IDENTITY_INSERT establecida en ON. Si una tabla ya tiene esta propiedad configurada y ON emites SET IDENTITY_INSERT ON para otra tabla, un error identifica la tabla para la que ya está establecida la propiedad.

Tras completar las inserciones explícitas, vuelve a colocar IDENTITY_INSERT y OFF ejecuta DBCC CHECKIDENT con RESEED para realinear el rango de identidad y evitar posibles conflictos con futuros valores generados automáticamente.

Fabric Data Warehouse no garantiza la unicidad de los valores de identidad cuando IDENTITY_INSERT se utiliza. Valores insertados explícitamente pueden introducir duplicados a menos que se realinee DBCC CHECKIDENT los metadatos de identidad antes de que el sistema genere más valores.

Permisos

Debes ser dueño de la mesa o tener ALTER permiso para que la ponga.

Limitaciones

SET IDENTITY_INSERT se aplica solo a INSERT las sentencias de Y COPY INTO . No permite actualizar los valores existentes de las columnas de identidad.

Ejemplos

A. Insertar valores centinela en una tabla de dimensiones

El uso más común es IDENTITY_INSERT poblar valores centinela, como -1 para "Desconocido", en tablas dimensionales durante la configuración o migración del almacén de datos.

-- Create a dimension table with an IDENTITY column
CREATE TABLE dbo.DimCustomer (
    CustomerKey BIGINT IDENTITY,
    CustomerName VARCHAR(100),
    Email VARCHAR(200)
);

-- Enable IDENTITY_INSERT to add sentinel rows
SET IDENTITY_INSERT dbo.DimCustomer ON;

INSERT INTO dbo.DimCustomer (CustomerKey, CustomerName, Email)
VALUES (-1, 'Unknown', 'N/A');

INSERT INTO dbo.DimCustomer (CustomerKey, CustomerName, Email)
VALUES (-2, 'Not Applicable', 'N/A');

SET IDENTITY_INSERT dbo.DimCustomer OFF;

-- Reseed to prevent conflicts with future auto-generated values
DBCC CHECKIDENT('dbo.DimCustomer', RESEED);

B. Migrar datos preservando los valores de identidad existentes

Al migrar desde SQL Server o Azure Synapse Analytics, utiliza IDENTITY_INSERT para preservar los valores de identidad existentes y mantener la integridad referencial.

-- Assume dbo.DimProduct has an IDENTITY column named ProductKey
SET IDENTITY_INSERT dbo.DimProduct ON;

INSERT INTO dbo.DimProduct (ProductKey, ProductName, Category, ListPrice)
VALUES (1, 'Widget A', 'Hardware', 19.99),
       (2, 'Widget B', 'Hardware', 29.99),
       (3, 'Gadget C', 'Electronics', 49.99);

SET IDENTITY_INSERT dbo.DimProduct OFF;

-- Reseed after migration
DBCC CHECKIDENT('dbo.DimProduct', RESEED);

C. Rellenar un vacío en los valores de identidad

Si se eliminan filas de una tabla, úsalas IDENTITY_INSERT para rellenar huecos en la secuencia identidad cuando sea necesario.

CREATE TABLE dbo.Tool (
    ID BIGINT IDENTITY,
    Name VARCHAR(40) NOT NULL
);

INSERT INTO dbo.Tool (Name)
VALUES ('Screwdriver'), ('Hammer'), ('Saw'), ('Shovel');

-- Delete a row, creating a gap
DELETE FROM dbo.Tool WHERE Name = 'Saw';

-- Fill the gap with an explicit value
SET IDENTITY_INSERT dbo.Tool ON;

INSERT INTO dbo.Tool (ID, Name)
VALUES (3, 'Garden shovel');

SET IDENTITY_INSERT dbo.Tool OFF;
DBCC CHECKIDENT('dbo.Tool', RESEED);

D. Insertar valores explícitos con COPY INTO

La COPY INTO instrucción permite la IDENTITY_INSERT opción de ingerir valores explícitos dentro del comando. COPY INTO las opciones anulan cualquier configuración a nivel de sesión para IDENTITY_INSERT.

COPY INTO dbo.Employees (EmployeeID 1, FirstName 2, LastName 3)
FROM 'https://myaccount.blob.core.windows.net/myblobcontainer/folder1/'
WITH (
    FILE_TYPE = 'CSV',
    IDENTITY_INSERT = 'ON'
);

Contenido relacionado

  • Last updated on