SET IDENTITY_INSERT (Transact-SQL)

Si applica a:SQL ServerDatabase SQL di AzureIstanza gestita di SQL di AzureAzure Synapse AnalyticsDatabase SQL in Microsoft Fabric

Usando questa istruzione, puoi inserire valori espliciti nella IDENTITY colonna di una tabella.

Questo articolo e la IDENTITY sintassi differiscono tra le diverse piattaforme del SQL motore di database. Per Microsoft Fabric Data Warehouse, seleziona Fabric Data Warehouse nell'elenco a tendina delle versioni.

Convenzioni relative alla sintassi Transact-SQL

Sintassi

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

Argomenti

database_name

Il nome del database in cui risiede la tabella specificata.

schema_name

Il nome dello schema che contiene la tabella.

table_name

Nome di una tabella con una colonna Identity.

Osservazioni:

In qualsiasi momento, solo una tabella di una sessione può avere la proprietà IDENTITY_INSERT impostata su ON. Se una tabella ha già impostato questa proprietà su ON, e si emette un'istruzione SET IDENTITY_INSERT ON per un'altra tabella, SQL Server restituisce un messaggio di errore che dichiara SET IDENTITY_INSERT già ONè , e riporta la tabella per cui ON è impostato.

  • Quando l'argomento increment della IDENTITY funzione è positivo e il valore inserito è maggiore del valore identità corrente della tabella, SQL motore di database utilizza automaticamente il nuovo valore inserito come valore identità corrente.
  • Quando l'argomento increment della IDENTITY funzione è negativo e il valore inserito è minore del valore identità corrente della tabella, SQL Server utilizza automaticamente il nuovo valore inserito come valore identità corrente.

L'impostazione di SET IDENTITY_INSERT viene impostata in fase di esecuzione o in fase di esecuzione e non in fase di analisi.

Autorizzazioni

Devi possedere il tavolo o avere ALTER il permesso di esserlo.

Esempi

Nell'esempio seguente viene creata una tabella contenente una colonna Identity e viene illustrato come tramite l'impostazione di SET IDENTITY_INSERT sia possibile completare un'interruzione nella sequenza di valori Identity generata da un'istruzione DELETE.

USE AdventureWorks2022;
GO

Creare una tabella degli strumenti.

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

Inserire valori nella tabella products.

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

Creare un gap nei valori Identity.

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

SELECT *
FROM dbo.Tool;
GO

Provare a inserire un valore ID esplicito pari a 3.

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

Il codice precedente INSERT restituisce il seguente errore:

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.

Impostare IDENTITY_INSERT su ON.

SET IDENTITY_INSERT dbo.Tool ON;
GO

Provare a inserire un valore ID esplicito pari a 3.

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

SELECT *
FROM dbo.Tool;
GO

Eliminare la tabella degli strumenti.

DROP TABLE dbo.Tool;
GO

Contenuto correlato

Si applica a:Warehouse in Microsoft Fabric

Usare SET IDENTITY_INSERT per inserire valori espliciti nella IDENTITY colonna di una tabella in Fabric Data Warehouse. Da usare SET IDENTITY_INSERT quando è necessario inserire valori specifici in una colonna identità, come durante la migrazione dei dati, il disaster recovery o quando si popolano i valori sentinel nelle tabelle delle dimensioni.

Convenzioni relative alla sintassi Transact-SQL

Sintassi

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

Argomenti

schema_name

Il nome dello schema che contiene la tabella.

table_name

Nome di una tabella con una colonna Identity.

Osservazioni:

In qualsiasi momento, solo una tabella di una sessione può avere la proprietà IDENTITY_INSERT impostata su ON. Se una tabella ha già impostata questa proprietà e ON la emetti SET IDENTITY_INSERT ON per un'altra tabella, un errore identifica la tabella per cui la proprietà è già impostata.

Dopo aver completato gli inserti espliciti, si torna IDENTITY_INSERT a OFF impostare e si esegue DBCC CHECKIDENT con RESEED per riallineare l'intervallo di identità e prevenire potenziali conflitti con i valori generati automaticamente in futuro.

Fabric Data Warehouse non garantisce l'unicità dei valori di identità quando IDENTITY_INSERT viene utilizzato. Valori esplicitamente inseriti possono introdurre duplicati a meno che tu non corri DBCC CHECKIDENT a riallineare i metadati dell'identità prima che il sistema generi altri valori.

Autorizzazioni

Devi possedere il tavolo o avere ALTER il permesso di esserlo.

Limitazioni

SET IDENTITY_INSERT Si applica solo alle INSERT affermazioni AND COPY INTO . Non permette di aggiornare i valori delle colonne identità esistenti.

Esempi

A. Inserire i valori delle sentinelle in una tabella delle dimensioni

L'uso più comune è IDENTITY_INSERT la popolazione dei valori sentinella, come -1 per "Sconosciuto", nelle tabelle delle dimensioni durante l'installazione o la migrazione del 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. Migra i dati preservando i valori di identità esistenti

Quando si migra da SQL Server o Azure Synapse Analytics, si utilizza IDENTITY_INSERT per preservare i valori di identità esistenti e mantenere l'integrità referenziale.

-- 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. Riempire una lacuna nei valori identità

Se le righe vengono eliminate da una tabella, usarlo IDENTITY_INSERT per riempire le lacune nella sequenza identità quando necessario.

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. Inserire valori espliciti con COPY INTO

L'istruzione COPY INTO supporta l'opzione IDENTITY_INSERT di ingerire valori espliciti all'interno del comando. COPY INTO Le opzioni sovrascrivono qualsiasi impostazione a livello di sessione per 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'
);

Contenuto correlato

  • Last updated on