SET IDENTITY_INSERT (Transact-SQL)

Aplica-se a:SQL ServerBanco de Dados SQL do AzureInstância Gerenciada SQL do AzureBanco de Dados SQL do Azure Synapse Analyticsno Microsoft Fabric

Ao usar esta instrução, pode inserir valores explícitos na IDENTITY coluna de uma tabela.

Este artigo e a sintaxe IDENTITY diferem em diferentes plataformas do SQL Database Engine. Para Microsoft Fabric Data Warehouse, selecione Fabric Data Warehouse na lista suspensa de versões.

Transact-SQL convenções de sintaxe

Sintaxe

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

Argumentos

database_name

O nome da base de dados onde reside a tabela especificada.

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 esta propriedade definida como ON, e emitir uma SET IDENTITY_INSERT ON instrução para outra tabela, o SQL Server devolve uma mensagem de erro que indica SET IDENTITY_INSERT que já ONé , e reporta a tabela para a qual ON está definida.

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

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

Permissões

Deves ser dono da mesa ou ter ALTER permissão para a colocar.

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

Criar 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 devolve 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 para 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

Soltar 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 Afirmaçõ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 precisar de inserir valores específicos numa coluna de identidade, como durante migração de dados, recuperação de desastres ou ao preencher valores sentinela em tabelas de dimensões.

Transact-SQL convenções de sintaxe

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 esta propriedade definida para ON e emitir SET IDENTITY_INSERT ON para outra tabela, um erro identifica a tabela para a qual a propriedade já está definida.

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

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

Permissões

Deves ser dono da mesa ou ter ALTER permissão para a colocar.

Limitations

SET IDENTITY_INSERT Aplica-se apenas a INSERT declarações AND COPY INTO . Não permite atualizar os valores existentes das colunas de identidade.

Exemplos

A. Insira valores sentinela numa tabela de dimensões

A utilização mais comum para IDENTITY_INSERT é preencher valores sentinela, como -1 para "Desconhecido", em tabelas de dimensões durante a configuração ou migração do 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 os valores de identidade existentes

Ao migrar do SQL Server ou do Azure Synapse Analytics, utilize 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 eliminadas de uma tabela, use 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. Inserir 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-se a qualquer definiçã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 DBCC (Transact-SQL)
• Tutorial: Use IDENTITY colunas em Fabric Data Warehouse
• Migrar IDENTITY colunas para Fabric Data Warehouse