SET IDENTITY_INSERT (Transact-SQL)

Aplica-se a: SQL ServerBanco de Dados SQL do Azure Instância Gerenciada de SQL do Azure Azure 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 }  

Observação

Para exibir a sintaxe do Transact-SQL para o SQL Server 2014 (12.x) e versões anteriores, confira a Documentação das versões anteriores.

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

Em qualquer momento, somente uma tabela em uma sessão pode ter a propriedade IDENTITY_INSERT definida como ON. Se uma tabela já possui essa propriedade definida como ON, e uma instrução SET IDENTITY_INSERT ON for emitida para outra tabela, o SQL Server retornará uma mensagem de erro declarando que SET IDENTITY_INSERT já é ON e informará para o que a tabela está definida como ON.

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 IDENTITY_INSERT é definida na execução ou em tempo de execução, e não em tempo de análise.

Permissões

O usuário deve ser proprietário da tabela ou ter a permissão ALTER 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  
-- Create tool table.  
CREATE TABLE dbo.Tool(  
   ID INT IDENTITY NOT NULL PRIMARY KEY,   
   Name VARCHAR(40) NOT NULL  
);  
GO  
-- Inserting values into products table.  
INSERT INTO dbo.Tool(Name)   
VALUES ('Screwdriver')  
        , ('Hammer')  
        , ('Saw')  
        , ('Shovel');  
GO  
  
-- Create a gap in the identity values.  
DELETE dbo.Tool  
WHERE Name = 'Saw';  
GO  
  
SELECT *   
FROM dbo.Tool;  
GO  
  
-- Try to insert an explicit ID value of 3;  
-- should return an 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.
INSERT INTO dbo.Tool (ID, Name) VALUES (3, 'Garden shovel');  
GO  
-- SET IDENTITY_INSERT to ON.  
SET IDENTITY_INSERT dbo.Tool ON;  
GO  
  
-- Try to insert an explicit ID value of 3.  
INSERT INTO dbo.Tool (ID, Name) VALUES (3, 'Garden shovel');  
GO  
  
SELECT *   
FROM dbo.Tool;  
GO  
-- Drop products table.  
DROP TABLE dbo.Tool;  
GO  

Consulte Também

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