SET IDENTITY_INSERT (Transact-SQL)

A következőkre vonatkozik:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsSQL-adatbázis a Microsoft Fabricben

Ezzel az állítással explicit értékeket lehet beilleszteni egy IDENTITY tábla oszlopába.

Ez a cikk és a IDENTITY szintaxis eltér az SQL Database Engine különböző platformjain. Microsoft Fabric Data Warehouse válaszd ki a verzió lebontott listájában a Fabric Data Warehouse-et.

Transact-SQL szintaxis konvenciói

Szintaxis

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

Érvek

database_name

Az adatbázis neve, ahol a megadott tábla található.

schema_name

A táblázatot tartalmazó séma neve.

table_name

Egy identitásoszlopot tartalmazó tábla neve.

Megjegyzések

Egy munkamenetben egyszerre csak egy tábla rendelkezhet a IDENTITY_INSERT tulajdonság ONértékével. Ha egy tábla már van beállítva ez a tulajdonság , ONés kiadsz egy SET IDENTITY_INSERT ON utasítást egy másik táblára, akkor az SQL Server egy hibaüzenetet ad vissza, amely azt mondjaSET IDENTITY_INSERT, hogy már ON, és jelentést tesz az adott tábláról.ON

  • Ha a increment függvény argumentuma IDENTITY pozitív, és a beillesztett érték nagyobb, mint a tábla aktuális identitásértéke, az SQL Database Engine automatikusan az új beillesztett értéket használja az aktuális identitásértékként.
  • Ha a increment függvény argumentuma IDENTITY negatív, és a beillesztett érték kisebb, mint a tábla jelenlegi identitásértéke, az SQL Server automatikusan az új beillesztett értéket használja jelenlegi identitásértékként.

A SET IDENTITY_INSERT beállítása végrehajtáskor vagy futtatáskor van beállítva, és nem elemzési időpontban.

Engedélyek

Neked kell az asztal tulajdonosa vagy engedélyed ALTER van rajta.

Példák

Az alábbi példa egy identitásoszlopot tartalmazó táblát hoz létre, és bemutatja, hogyan használható a SET IDENTITY_INSERT beállítás a DELETE utasítás által okozott identitásértékek közötti rés kitöltésére.

USE AdventureWorks2022;
GO

Eszköztábla létrehozása.

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

Értékek beszúrása a termékek táblájába.

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

Hozzon létre egy rést az identitásértékekben.

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

SELECT *
FROM dbo.Tool;
GO

Próbáljon meg beszúrni egy 3-ás explicit azonosítóértéket.

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

Az előző INSERT kód a következő hibát adja vissza:

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.

Állítsa IDENTITY_INSERTONértékre.

SET IDENTITY_INSERT dbo.Tool ON;
GO

Próbáljon meg beszúrni egy 3-ás explicit azonosítóértéket.

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

SELECT *
FROM dbo.Tool;
GO

Eszköztábla elvetése.

DROP TABLE dbo.Tool;
GO

Kapcsolódó tartalom

A következőkre vonatkozik:Warehouse a Microsoft Fabricban

Használd SET IDENTITY_INSERT explicit értékeket beilleszteni egy IDENTITY tábla oszlopába Fabric Data Warehouse-ben. Használd SET IDENTITY_INSERT , amikor konkrét értékeket kell beillesztened egy identitásoszlopba, például adatmigráció, katasztrófa-helyreállítás vagy sentinel értékek feltöltése során dimenziótáblákban.

Transact-SQL szintaxis konvenciói

Szintaxis

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

Érvek

schema_name

A táblázatot tartalmazó séma neve.

table_name

Egy identitásoszlopot tartalmazó tábla neve.

Megjegyzések

Egy munkamenetben egyszerre csak egy tábla rendelkezhet a IDENTITY_INSERT tulajdonság ONértékével. Ha egy tábla már beállította ezt a tulajdonságot, ON és egy másik táblára adsz ki, SET IDENTITY_INSERT ON hiba azonosítja azt a táblát, amelyhez a tulajdonság már be van állítva.

Az explicit beillesztések befejezése után állítsd IDENTITY_INSERT vissza és OFF futtasd a DBCC CHECKIDENT programot RESEED , hogy újraigazítsuk az identitástartományt, és elkerüld a jövőben automatikusan generált értékekkel való esetleges ütközéseket.

Fabric Data Warehouse nem garantálja az identitásértékek egyediségét, ha IDENTITY_INSERT használják. Explicit értékek duplikátumokat hozhatnak létre, hacsak nem futunk DBCC CHECKIDENT az identitás metaadatainak újraigazításához, mielőtt a rendszer több értéket generálna.

Engedélyek

Neked kell az asztal tulajdonosa vagy engedélyed ALTER van rajta.

Limitations

SET IDENTITY_INSERT Csak INSERT a és COPY INTO állításokra vonatkozik. Nem engedi a meglévő identitásoszlop értékek frissítését.

Példák

A. Sentinel értékek bekerülése egy dimenziótáblázatba

A leggyakoribb felhasználás IDENTITY_INSERT az sentinel értékek feltöltése, például -1 az "Ismeretlen" esetében, a dimenziótáblákban adatraktár beállítása vagy migrációja során.

-- 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. Adatmigráció a meglévő identitásértékek megőrzése mellett

Az SQL Server-ről vagy az Azure Synapse Analytics-ről való migrációkor használd IDENTITY_INSERT a meglévő identitásértékek megőrzésére és a referenciális integritás megőrzésére.

-- 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. Töltsd be az identitásértékek közötti rést

Ha sorokat törölnek egy táblázatból, akkor IDENTITY_INSERT szükség esetén kitöltsék az identitássorozat réseit.

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. Explicit értékek beadása a COPY INTO gombbal

Az COPY INTO utasítás támogatja a IDENTITY_INSERT parancson belüli explicit értékek bevételének lehetőségét. COPY INTOAz opciók felülírják bármely játékmenet-szintű beállítást .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'
);

Kapcsolódó tartalom

  • Last updated on