SET IDENTITY_INSERT (Transact-SQL)

gäller för:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsSQL-databas i Microsoft Fabric

Genom att använda detta uttalande kan du infoga explicita värden i kolumnen IDENTITY i en tabell.

Denna artikel och syntaxen skiljer IDENTITY sig åt på olika plattformar i SQL Database Engine. För Microsoft Fabric Data Warehouse, välj Fabric Data Warehouse i versionslistan.

Transact-SQL syntaxkonventioner

Syntax

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

Argument

database_name

Namnet på databasen där den angivna tabellen finns.

schema_name

Namnet på schemat som innehåller tabellen.

table_name

Namnet på en tabell med en identitetskolumn.

Anmärkningar

När som helst kan endast en tabell i en session ha egenskapen IDENTITY_INSERT inställd på ON. Om en tabell redan har denna egenskap satt till ON, och du gör en SET IDENTITY_INSERT ON sats för en annan tabell, returnerar SQL Server ett felmeddelande som säger SET IDENTITY_INSERT är redan ON, och rapporterar tabellen för vilken ON den är satt.

  • När argumentet increment för IDENTITY funktionen är positivt, och det insatta värdet är större än det aktuella identitetsvärdet för tabellen, använder SQL Database Engine automatiskt det nya insatta värdet som det aktuella identitetsvärdet.
  • När argumentet increment för IDENTITY funktionen är negativt och det insatta värdet är mindre än det aktuella identitetsvärdet för tabellen, använder SQL Server automatiskt det nya insatta värdet som det aktuella identitetsvärdet.

Inställningen för SET IDENTITY_INSERT anges vid körnings- eller körningstid och inte vid parsningstid.

Behörigheter

Du måste äga bordet eller ha ALTER tillstånd på bordet.

Exempel

I följande exempel skapas en tabell med en identitetskolumn och visar hur inställningen SET IDENTITY_INSERT kan användas för att fylla ett tomrum i identitetsvärdena som orsakas av en DELETE-instruktion.

USE AdventureWorks2022;
GO

Skapa verktygstabell.

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

Infoga värden i produkttabellen.

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

Skapa en lucka i identitetsvärdena.

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

SELECT *
FROM dbo.Tool;
GO

Försök att infoga ett explicit ID-värde på 3.

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

Föregående INSERT kod ger följande fel:

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.

Ange IDENTITY_INSERT till ON.

SET IDENTITY_INSERT dbo.Tool ON;
GO

Försök att infoga ett explicit ID-värde på 3.

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

SELECT *
FROM dbo.Tool;
GO

Ta bort verktygstabell.

DROP TABLE dbo.Tool;
GO

Relaterat innehåll

gäller för:Warehouse i Microsoft Fabric

Använd SET IDENTITY_INSERT för att infoga explicita värden i kolumnen IDENTITY i en tabell i Fabric Data Warehouse. Använd SET IDENTITY_INSERT när du behöver infoga specifika värden i en identitetskolumn, till exempel under datamigrering, katastrofåterställning eller när du fyller i sentinelvärden i dimensionstabeller.

Transact-SQL syntaxkonventioner

Syntax

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

Argument

schema_name

Namnet på schemat som innehåller tabellen.

table_name

Namnet på en tabell med en identitetskolumn.

Anmärkningar

När som helst kan endast en tabell i en session ha egenskapen IDENTITY_INSERT inställd på ON. Om en tabell redan har denna egenskap satt till ON och du ger en fråga SET IDENTITY_INSERT ON för en annan tabell, identifierar ett fel tabellen för vilken egenskapen redan är satt.

Efter att ha slutfört explicita insättningar, återställ IDENTITY_INSERT till OFF och kör DBCC CHECKIDENT med RESEED för att justera identitetsintervallet och förhindra potentiella konflikter med framtida automatiskt genererade värden.

Fabric Data Warehouse garanterar inte unikhet i identitetsvärden när IDENTITY_INSERT den används. Explicit insatta värden kan introducera dubbletter om du inte kör DBCC CHECKIDENT för att justera identitetsmetadata innan systemet genererar fler värden.

Behörigheter

Du måste äga bordet eller ha ALTER tillstånd på bordet.

Limitations

SET IDENTITY_INSERT gäller endast för INSERT och COPY INTO påståenden. Den tillåter dig inte att uppdatera befintliga identitetskolumnvärden.

Exempel

A. Infoga sentinelvärden i en dimensionstabell

Den vanligaste användningen är IDENTITY_INSERT att fylla i sentinelvärden, till exempel -1 för "Okänd", i dimensionstabeller under installation eller migrering av datalager.

-- 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. Migrera data samtidigt som befintliga identitetsvärden bevaras

Vid migrering från SQL Server eller Azure Synapse Analytics, använd IDENTITY_INSERT för att bevara befintliga identitetsvärden och upprätthålla referensintegritet.

-- 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. Fyll ett gap i identitetsvärden

Om rader tas bort från en tabell, använd IDENTITY_INSERT den för att fylla luckor i identitetssekvensen när det behövs.

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. Infoga explicita värden med COPY INTO

Satsen COPY INTO stöder IDENTITY_INSERT möjligheten att ta in explicita värden i kommandot. COPY INTO Alternativ åsidosätter alla sessionsnivåinställningar för 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'
);

Relaterat innehåll

  • Last updated on