Compartilhar via

SET IDENTITY_INSERT (Transact-SQL)

  • Artigo

Aplica-se a:SQL ServerBanco de Dados SQL do AzureInstância Gerenciada de SQL do AzureAzure Synapse Analytics

Permite inserir valores explícitos na coluna de identidade de uma tabela.

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 no qual a tabela especificada reside.

schema_name

O nome do esquema ao qual a tabela pertence.

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 ONe uma instrução SET IDENTITY_INSERT ON for emitida para outra tabela, o SQL Server retornará uma mensagem de erro informando que SET IDENTITY_INSERT já está ONe relatará a tabela para a qual ON está definida.

Se o valor inserido for maior que o valor de identidade atual para a tabela, o SQL Server usará automaticamente o novo valor inserido como 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

O usuário deve ser o proprietário da tabela ou ter ALTER permissão na tabela.

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 INSERT anterior deve retornar 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