SET IDENTITY_INSERT (Transact-SQL)

Aplica-se a:SQL ServerBanco de Dados SQL do AzureInstância Gerenciada de SQL do AzureAzure Synapse AnalyticsBanco de Dados SQL no Microsoft Fabric

Usando essa sentença, você pode inserir valores explícitos na IDENTITY coluna de uma tabela.

Este artigo e a IDENTITY sintaxe diferem entre diferentes plataformas do SQL Mecanismo de Banco de Dados. Para Microsoft Fabric Data Warehouse, selecione Fabric Data Warehouse na lista suspensa de versões.

Convenções de sintaxe de Transact-SQL

Sintaxe

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

Argumentos

database_name

O nome do banco de dados onde a tabela especificada reside.

schema_name

O nome do esquema que contém a tabela.

table_name

O nome de uma tabela com uma coluna de identidade.

Comentários

A qualquer momento, apenas uma tabela em uma sessão pode ter a propriedade IDENTITY_INSERT definida como ON. Se uma tabela já tiver essa propriedade definida como ON, e você emite uma SET IDENTITY_INSERT ON instrução para outra tabela, o SQL Server retorna uma mensagem de erro que afirma SET IDENTITY_INSERT que já ONestá , e reporta a tabela para a qual ON está definida.

• Quando o argumento increment da IDENTITY função é positivo e o valor inserido é maior que o valor de identidade atual da tabela, o SQL Mecanismo de Banco de Dados usa automaticamente o novo valor inserido como o valor de identidade atual.
• Quando o argumento increment da IDENTITY função é negativo e o valor inserido é menor que o valor de identidade atual da tabela, o SQL Server automaticamente usa o novo valor inserido como o valor de identidade atual.

A configuração de SET IDENTITY_INSERT é definida em tempo de execução ou de execução e não em tempo de análise.

Permissões

Você deve ser dono da mesa ou ter ALTER permissão para a mesa.

Exemplos

O exemplo a seguir cria uma tabela com uma coluna de identidade e mostra como a configuração SET IDENTITY_INSERT pode ser usada para preencher uma lacuna nos valores de identidade causados por uma instrução DELETE.

USE AdventureWorks2022;
GO

Criar tabela de ferramentas.

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

Insira valores na tabela de produtos.

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

Crie uma lacuna nos valores de identidade.

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

SELECT *
FROM dbo.Tool;
GO

Tente inserir um valor de ID explícito de 3.

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

O código anterior INSERT retorna o seguinte erro:

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.

Defina IDENTITY_INSERT como ON.

SET IDENTITY_INSERT dbo.Tool ON;
GO

Tente inserir um valor de ID explícito de 3.

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

SELECT *
FROM dbo.Tool;
GO

Solte a tabela de ferramentas.

DROP TABLE dbo.Tool;
GO

Conteúdo relacionado

• CREATE TABLE (Transact-SQL)
• CREATE TABLE (Transact-SQL) IDENTITY (Propriedade)
• SCOPE_IDENTITY (Transact-SQL)
• INSERT (Transact-SQL)
• SET Instruções (Transact-SQL)

Aplica-se a:Warehouse no Microsoft Fabric

Use SET IDENTITY_INSERT para inserir valores explícitos na IDENTITY coluna de uma tabela em Fabric Data Warehouse. Use SET IDENTITY_INSERT quando for necessário inserir valores específicos em uma coluna de identidade, como durante migração de dados, recuperação de desastres ou ao preencher valores sentinela em tabelas de dimensões.

Convenções de sintaxe de Transact-SQL

Sintaxe

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

Argumentos

schema_name

O nome do esquema que contém a tabela.

table_name

O nome de uma tabela com uma coluna de identidade.

Comentários

A qualquer momento, apenas uma tabela em uma sessão pode ter a propriedade IDENTITY_INSERT definida como ON. Se uma tabela já tiver essa propriedade definida como ON e você emitir SET IDENTITY_INSERT ON para outra tabela, um erro identifica a tabela para a qual a propriedade já está definida.

Após concluir as inserções explícitas, volte IDENTITY_INSERT a OFF e execute o DBCC CHECKIDENT com RESEED para realinhar o intervalo de identidade e evitar possíveis conflitos com futuros valores gerados automaticamente.

Fabric Data Warehouse não garante a unicidade dos valores de identidade quando IDENTITY_INSERT é usada. Valores inseridos explicitamente podem introduzir duplicatas, a menos que você execute DBCC CHECKIDENT para realinhar metadados de identidade antes que o sistema gere mais valores.

Permissões

Você deve ser dono da mesa ou ter ALTER permissão para a mesa.

Limitações

SET IDENTITY_INSERT Aplica-se apenas a INSERT declarações AND COPY INTO . Não permite que você atualize valores de colunas de identidade existentes.

Exemplos

A. Insira valores sentinela em uma tabela de dimensões

O uso mais comum é IDENTITY_INSERT preencher valores sentinela, como -1 para "Desconhecido", em tabelas de dimensões durante a configuração ou migração de data warehouse.

-- 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 dados preservando valores de identidade existentes

Ao migrar do SQL Server ou do Azure Synapse Analytics, use IDENTITY_INSERT para preservar valores de identidade existentes e manter a integridade 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. Preencher uma lacuna nos valores de identidade

Se as linhas forem excluídas de uma tabela, use-o IDENTITY_INSERT para preencher lacunas na sequência de identidade quando necessário.

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. Insira valores explícitos com COPY INTO

A COPY INTO instrução suporta a IDENTITY_INSERT opção de ingerir valores explícitos dentro do comando. COPY INTO As opções sobrepõem qualquer configuração de nível de sessão 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'
);

Conteúdo relacionado

• IDENTITYColunas em Fabric Data Warehouse
• VERIFICADOR DE DBCC (Transact-SQL)
• Tutorial: Use IDENTITY colunas em Fabric Data Warehouse
• Migrar IDENTITY colunas para Fabric Data Warehouse